Allow Duplicates

In message <1127630A-A798-475A-AD09-732220939122.DeleteThis@microsoft.com>, acss
<joekru98.DeleteThis@hotmail.com> writes
>i have a table for invoices and at times i receive a duplicate invoice number
>from another country. How do i set up a primary key (InvoiceID) and a
>secondary key (country) that would allow a duplicate entry only if from a
>different country?

I'm assuming that InvoiceID is not an autonumber field.

Open the table in design view. Select both the InvoiceID and Country
fields (by shift- or Ctrl-clicking.) Click on the Primary Key button.
You should see the key symbol beside both of the fields.


--
Bernard Peek

You are correct that it is not autonumber and is unique though duplication
happens from time to time dependant on country submitting invoices and still
need to be recorded regardless. I am receiving the error that there is
already a relationship so i must delete it before assigning these two keys.
Thanks

"Bernard Peek" wrote:

> In message <1127630A-A798-475A-AD09-732220939122.RemoveThis@microsoft.com>, acss
> <joekru98.RemoveThis@hotmail.com> writes
> >i have a table for invoices and at times i receive a duplicate invoice number
> >from another country. How do i set up a primary key (InvoiceID) and a
> >secondary key (country) that would allow a duplicate entry only if from a
> >different country?
>
> I'm assuming that InvoiceID is not an autonumber field.
>
> Open the table in design view. Select both the InvoiceID and Country
> fields (by shift- or Ctrl-clicking.) Click on the Primary Key button.
> You should see the key symbol beside both of the fields.
>
>
> --
> Bernard Peek
>

If your Invoices table takes part on the primary key side of one or more
relationships, then I don't think the multi-field PK is a good idea. It
would mean that you have to add the Country field to the foreign key(s) in
the other table(s) as well, which could make a larger headache for you.

Also, since a field in a PK cannot be null, you would need to ensure ALL of
your records had a value in the Country field.

I suggest you go with Noëlla's idea and add an Autonumber field - InvoiceID.
(If your existing field is already called InvoiceID then rename it to
InvoiceNum or suchlike - I prefer to reserve the "ID" suffix for autonumber
fields as an aid to documentation).

After adding the autonumber field, save the table (but don't try to change
the primary key yet). Then edit the design of your related table(s) and add
a long integer field "InvoiceFK" for the foriegn key.

Now create an update query to populate the new field. Its SQL will look
something like this:

UPDATE [InvoiceTable] INNER JOIN [OtherTable] ON
[InvoiceTable].[InvoiceNum] = [OtherTable].[OldForeignKeyField]
SET [OtherTable].[InvoiceFK] = [InvoiceTable].[InvoiceID];

Then you can remove the old relationship, then change your PK to InvoiceID
and add a new relationship joining the two new fields.

Finally, you can add a composite index for the two fields, InvoiceNum and
Country, and delete the old FK field from the other table.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

"acss" <joekru98 DeleteThis @hotmail.com> wrote in message
news:260C45E2-37C5-4787-8C60-AC24BFBF8B20@microsoft.com...
> You are correct that it is not autonumber and is unique though duplication
> happens from time to time dependant on country submitting invoices and
> still
> need to be recorded regardless. I am receiving the error that there is
> already a relationship so i must delete it before assigning these two
> keys.
> Thanks
>
> "Bernard Peek" wrote:
>
>> In message <1127630A-A798-475A-AD09-732220939122 DeleteThis @microsoft.com>, acss
>> <joekru98 DeleteThis @hotmail.com> writes
>> >i have a table for invoices and at times i receive a duplicate invoice
>> >number
>> >from another country. How do i set up a primary key (InvoiceID) and a
>> >secondary key (country) that would allow a duplicate entry only if from
>> >a
>> >different country?
>>
>> I'm assuming that InvoiceID is not an autonumber field.
>>
>> Open the table in design view. Select both the InvoiceID and Country
>> fields (by shift- or Ctrl-clicking.) Click on the Primary Key button.
>> You should see the key symbol beside both of the fields.
>>
>>
>> --
>> Bernard Peek
>>

Hello,
I am still working on this and i am curious as to how you create a unique
index on a combination of invoice and country field? The primary key is the
invoice number as an autonumber so the table can not have more than one
primary key....can you eloborate for me please?

"Noëlla Gabriël" wrote:

> Hi,
>
> I always take an autonumber field as PK field. You can then create another
> unique index on the combination of the invoice number field and the country
> field
> --
> Kind regards
> Noëlla
>
>
> "acss" wrote:
>
> > i have a table for invoices and at times i receive a duplicate invoice number
> > from another country. How do i set up a primary key (InvoiceID) and a
> > secondary key (country) that would allow a duplicate entry only if from a
> > different country?
> >
> > Thanks

Hi Joe (?)

I'm confused. You say "The primary key is the invoice number as an
autonumber" but you also said that the invoice number was in the imported
data, so it can't be an autonumber!

I suggest you add a new autonumber field named InvoiceID and make that your
primary key. Then, make InvoiceNumber and Country both required fields and
make them a composite unique key.

To do this, open the Indexes window (View>Indexes) and in the first blank
row, enter "InvoiceNumberCountry" as the index name and select InvoiceNumber
as the field name. Then, in the index properties below, change Unique to
"Yes". Now, in the next blank line, choose Country as the field name (but
leave index name blank).

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


"acss" <joekru98.TakeThisOut@hotmail.com> wrote in message
news:C085BA73-567F-4527-95ED-A084EC3E3670@microsoft.com...
> Hello,
> I am still working on this and i am curious as to how you create a unique
> index on a combination of invoice and country field? The primary key is
> the
> invoice number as an autonumber so the table can not have more than one
> primary key....can you eloborate for me please?
>
> "Noëlla Gabriël" wrote:
>
>> Hi,
>>
>> I always take an autonumber field as PK field. You can then create
>> another
>> unique index on the combination of the invoice number field and the
>> country
>> field
>> --
>> Kind regards
>> Noëlla
>>
>>
>> "acss" wrote:
>>
>> > i have a table for invoices and at times i receive a duplicate invoice
>> > number
>> > from another country. How do i set up a primary key (InvoiceID) and a
>> > secondary key (country) that would allow a duplicate entry only if from
>> > a
>> > different country?
>> >
>> > Thanks

Apologies for the confusion Graham,

I would like for the primary key to be unique in the invoice table and as a
foreign key in the details Table however i have the challenge that duplicate
invoices need to be entered and should be accepted only if it is from a
different country.

InvTable
InvID---PK
InvCtry
Invdate
etc

InvDetails
InvID-FK
ProductID--FK---COMPOSITE KEY consisting of InvID and ProductID
Price
Qty

ProductTable
ProductID-----PK-Autonumber
ProductName
Category
Etc

I am stuck since i need a one to many relationship between Inv and
details(one invoice can have many details).The problem occurs as the same
invoice number needs to be entered 3 times once for china another for ecuador
and another for brazil..that is why i need acess to accept same invoice
number only if by different countries. Can you suggest some direction please?

Thanks,


"Graham Mandeno" wrote:

> Hi Joe (?)
>
> I'm confused. You say "The primary key is the invoice number as an
> autonumber" but you also said that the invoice number was in the imported
> data, so it can't be an autonumber!
>
> I suggest you add a new autonumber field named InvoiceID and make that your
> primary key. Then, make InvoiceNumber and Country both required fields and
> make them a composite unique key.
>
> To do this, open the Indexes window (View>Indexes) and in the first blank
> row, enter "InvoiceNumberCountry" as the index name and select InvoiceNumber
> as the field name. Then, in the index properties below, change Unique to
> "Yes". Now, in the next blank line, choose Country as the field name (but
> leave index name blank).
>
> --
> Good Luck
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
>
> "acss" <joekru98.DeleteThis@hotmail.com> wrote in message
> news:C085BA73-567F-4527-95ED-A084EC3E3670@microsoft.com...
> > Hello,
> > I am still working on this and i am curious as to how you create a unique
> > index on a combination of invoice and country field? The primary key is
> > the
> > invoice number as an autonumber so the table can not have more than one
> > primary key....can you eloborate for me please?
> >
> > "Noëlla Gabriël" wrote:
> >
> >> Hi,
> >>
> >> I always take an autonumber field as PK field. You can then create
> >> another
> >> unique index on the combination of the invoice number field and the
> >> country
> >> field
> >> --
> >> Kind regards
> >> Noëlla
> >>
> >>
> >> "acss" wrote:
> >>
> >> > i have a table for invoices and at times i receive a duplicate invoice
> >> > number
> >> > from another country. How do i set up a primary key (InvoiceID) and a
> >> > secondary key (country) that would allow a duplicate entry only if from
> >> > a
> >> > different country?
> >> >
> >> > Thanks
>
>
>