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
|
|||
|
|||
Same invoice number- different country
I have an invoice table with primary key of 5 digit text that is unique and i
have a (customer id foreign key in invoice table)customer table that primary is 5 digit text unique. At times i receive invoices from different countries for the same customer that have same invoice ID. How do i set up the invoice table to allow duplicates even though same Invoice ID from different country? Do i utilize a composite key..how is this set up? |
#2
|
|||
|
|||
Same invoice number- different country
Your invoice table should look like:
TblInvoice InvoiceID (autonumber - PK) InvoiceDate CustomerID InvoiceNumber Your 5 digit text goes in the InvoiceNumber field and you can have duplicates in that field. InvoiceID makes every invoice record unique. Steve "acss" wrote in message ... I have an invoice table with primary key of 5 digit text that is unique and i have a (customer id foreign key in invoice table)customer table that primary is 5 digit text unique. At times i receive invoices from different countries for the same customer that have same invoice ID. How do i set up the invoice table to allow duplicates even though same Invoice ID from different country? Do i utilize a composite key..how is this set up? |
#3
|
|||
|
|||
Same invoice number- different country
"acss" wrote in message
... I have an invoice table with primary key of 5 digit text that is unique and i have a (customer id foreign key in invoice table)customer table that primary is 5 digit text unique. At times i receive invoices from different countries for the same customer that have same invoice ID. How do i set up the invoice table to allow duplicates even though same Invoice ID from different country? Do i utilize a composite key..how is this set up? If each invoice can have many iterations then I would suggest having a new table to contain them, since this represents a one-to-many relationship. Keith. www.keithwilby.co.uk |
#4
|
|||
|
|||
Same invoice number- different country
You *can* make a composite key, but I don't recommend this. Anyway, if
you want to do this, open the table in design mode. Choose the two rows that have country and the invoice number. Then click on the "primary key" button on the toolbar. This will make the composite key. I think it's much better NOT to do this. Here is the approach I'd take. 1) Setup a new table called tblCountries with the following fields * CountryCode primary key, text of length 2 (indexed using only unique values) * CountryName text of appropriate length for a country name The country code is the Internet code for the country (US = United States, JP=Japan, IN=India, etc.) Populate this table with data. 2) Setup another table called tblInvoice with the following fields: * InvoiceID autonumber, primary key * Country foreign key to tblCountries * InvoiceNumber Text of length 5 (if you want a 5 digit number) * Plus any Data fields 3) Now, create a form called frmInvoice, with it's data source set as table invoice. 4) Select the InvoiceID field on the form. Set the property "Locked" to true. 5) Create a "BeforeUpdate" event for the form. You can do this by click the "..." next to the forms "BeforeUpdate" event. 6) This opens a VBA module. You should paste in the following. Me.InvoiceID = Me.Country & "_" & Me.InvoiceNumber So say you get an invoice number 89031 from Spain. You invoice ID will become ES_89031 That should take care of your problem. In other forms and reports you could create some logic to always check the country and invoicenumber to get the invoice ID. Or you could just train people on the new invoice ID field. In article , acss wrote: I have an invoice table with primary key of 5 digit text that is unique and i have a (customer id foreign key in invoice table)customer table that primary is 5 digit text unique. At times i receive invoices from different countries for the same customer that have same invoice ID. How do i set up the invoice table to allow duplicates even though same Invoice ID from different country? Do i utilize a composite key..how is this set up? |
Thread Tools | |
Display Modes | |
|
|