Same invoice number- different country

[acss]

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?

 

[Steve]

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
(e-mail address removed)

 

[Keith Wilby]

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

 

[Daniel Haley]

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.