If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Allow Duplicates
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 |
#2
|
|||
|
|||
Allow Duplicates
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 |
#3
|
|||
|
|||
Allow Duplicates
In message , acss
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 |
#4
|
|||
|
|||
Allow Duplicates
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 , acss 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 |
#5
|
|||
|
|||
Allow Duplicates
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" wrote in message ... 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 , acss 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 |
#6
|
|||
|
|||
Allow Duplicates
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 |
#7
|
|||
|
|||
Allow Duplicates
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 (ViewIndexes) 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" wrote in message ... 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 |
#8
|
|||
|
|||
Allow Duplicates
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 (ViewIndexes) 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" wrote in message ... 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 |
#9
|
|||
|
|||
Allow Duplicates
On Tue, 4 Aug 2009 18:34:01 -0700, acss wrote:
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? It sounds like you need a composite, two-field primary key (InvoiceNo and CountryCode), which will also require a composite, two-field foreign key in the details table. You can use InvCtry;InvID as the Master Link Field and Child Link Field property of your subform. You will not be able to use an Autonumber for InvID, but then it's inappropriate to use an autonumber for that purpose in any case. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Allow Duplicates
My understanding was exactly correct then, and my suggestion still stands:
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. Your tables would then look like this: InvTable -------- InvID---PK - Autonumber InvNum (as imported - used to be InvID) InvCtry Invdate etc InvDetails ---------- InvID-FK (long numeric related to the *new* InvID) ProductID--FK---COMPOSITE PK consisting of InvID and ProductID Price Qty Add a composite unique key comprising InvNum and InvCtry as I described yesterday. John V has suggested another alternative which is to add InvCtry to InvDetails and have a two-field relationship. This is perfectly valid, but my preference is (a) to avoid multi-field relationships as they can become terribly messy and (b) to avoid using imported data as primary keys. The reason for (b) is that you never know when the rules are going to change - for example, your invoice number might be a numeric field and then the head office in Brazil decides to use alpha characters in its invoice numbers, so you need to change the data type in several places, not just one, and destroy and recreate relationships. Or perhaps in China they decide that they will reuse invoice numbers starting at the beginning of each year, so you need to add another field (InvYear) to your PK and also add it to your related tables. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "acss" wrote in message ... 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 (ViewIndexes) 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" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|