Autonumber 0ne-One

I have an invoice table and an invoice details table and i need to have the
design accept duplicate invoice numbers since we receive same invoices from
customer but from different countries. I have tried autonumber as primary key
in both tables (InvID) & (DetailId) yet the relationship is one to one. One
invoice may have many details so i am looking for a one to many relationship
since my results are data entry allows same invoice number yet details appear
the same on each entry. Can anyone assist on this ?

Thank you

On Aug 1, 5:06*pm, acss wrote:
I have an invoice table and an invoice details table and i need to have the
design accept duplicate invoice numbers since we receive same invoices from
customer but from different countries. I have tried autonumber as primary key
in both tables (InvID) & (DetailId) yet the relationship is one to one. One
invoice may have many details so i am looking for a one to many relationship
since my results are data entry allows same invoice number yet details appear
the same on each entry. Can anyone assist on this ?

Thank you

You can't have duplicate Invoice numbers - otherwise, how do you
relate an invoice's detail records back to it? You would have to do
something like making Invoice number and country the joint primary
key. And then you couldn't have an autonumber, but would have to use
DMax or similar to generate the next invoice number.

I have read here on this site that having a primary key as autonumber would
resolve this problem that i need to allow duplicates so in this case
autonumber would be the best solution yet since i need details i can not have
a one to one relationship. Is there a way to have a one to many relationship
using autonumber as primary???

"Piet Linden" wrote:

On Aug 1, 5:06 pm, acss wrote:
I have an invoice table and an invoice details table and i need to have the
design accept duplicate invoice numbers since we receive same invoices from
customer but from different countries. I have tried autonumber as primary key
in both tables (InvID) & (DetailId) yet the relationship is one to one. One
invoice may have many details so i am looking for a one to many relationship
since my results are data entry allows same invoice number yet details appear
the same on each entry. Can anyone assist on this ?

Thank you

You can't have duplicate Invoice numbers - otherwise, how do you
relate an invoice's detail records back to it? You would have to do
something like making Invoice number and country the joint primary
key. And then you couldn't have an autonumber, but would have to use
DMax or similar to generate the next invoice number.

Your question and an an example of the tables you need were given to you a
couple of days ago.

TblInvoice
InvoiceID (Autonumber)
InvoiceNumber
etc

TblInvoiceDetail
InvoiceDetailID
InvoiceID (Number - Long Integer)
etc

Steve



"acss" wrote in message
...
I have read here on this site that having a primary key as autonumber would
resolve this problem that i need to allow duplicates so in this case
autonumber would be the best solution yet since i need details i can not
have
a one to one relationship. Is there a way to have a one to many
relationship
using autonumber as primary???

"Piet Linden" wrote:

On Aug 1, 5:06 pm, acss wrote:
I have an invoice table and an invoice details table and i need to have
the
design accept duplicate invoice numbers since we receive same invoices
from
customer but from different countries. I have tried autonumber as
primary key
in both tables (InvID) & (DetailId) yet the relationship is one to one.
One
invoice may have many details so i am looking for a one to many
relationship
since my results are data entry allows same invoice number yet details
appear
the same on each entry. Can anyone assist on this ?

Thank you

You can't have duplicate Invoice numbers - otherwise, how do you
relate an invoice's detail records back to it? You would have to do
something like making Invoice number and country the joint primary
key. And then you couldn't have an autonumber, but would have to use
DMax or similar to generate the next invoice number.

Thank you Steve. By making the design changes i continue to have a one to one
relationship and not a one to many. I need to enter the same invoice number
several times depending on 3 countries ex: 10234-china 10234-ecuador 10234.
The invoice 10234 is the same and from the same customer yet from a different
country. The details on each invoice is what has the we need to capture so
having a one to one will not allow the details to change even though it is a
different record. Is there anything i am missing here on what to do? It is
only two tables invoice and details.



"Steve" wrote:

Community Message Not Available

On Sat, 1 Aug 2009 18:23:01 -0700, acss wrote:

Thank you Steve. By making the design changes i continue to have a one to one
relationship and not a one to many. I need to enter the same invoice number
several times depending on 3 countries ex: 10234-china 10234-ecuador 10234.
The invoice 10234 is the same and from the same customer yet from a different
country. The details on each invoice is what has the we need to capture so
having a one to one will not allow the details to change even though it is a
different record. Is there anything i am missing here on what to do? It is
only two tables invoice and details.

You're contradicting yourself.

You say you want one to one, which means ONE detail record.
You also say you want THREE detail records, one for each country.

You can't have it both ways.

I would presume that there is some information that is common to all the
invoices under a given invoice number, and some other information that is
country-specific. Is that the case?

Is there a separate Details table in addition to what you're describing?

--

John W. Vinson [MVP]

On Sat, 1 Aug 2009 20:00:01 -0700, acss wrote:

Thanks John,

I have to enter the same invoice number several times and though it is from
the same customer ID it is from a different country. The current design that
i now have is Invoice Table, Invoice Details Table and Customers Table. I
have tried using autonumber as the primary key between Invoice and Details
table yet i need it to be one to many and not one to one. Is there a way to
have duplicates in the invoice table or a certain type of relationship to
accept duplicates?

A primary key applies to ONE table - your "primary key between" phrase is
meaningless. Your details table should have an InvoiceID - Long Integer if the
InvoiceID is an Autonumber - as a foreign key field, NOT its primary key; you
cannot (absolutely not!) use a link from an autonumber in one table to an
autonumber in another table.

A primary key HAS NO DUPLICATES. Period. End of story. That's it's definition:
a UNIQUE (only one!) identifier for a record.

If you want more than one record with the same invoice number then you cannot
use an autonumber and you cannot make it the primary key.

Autonumbers are generally not suitable for human consumption; they will always
have gaps, and auditors get really creepy when they see gaps in a run of
invoice numbers.
--

John W. Vinson [MVP]

Thanks for the support.

"John W. Vinson" wrote:

On Sat, 1 Aug 2009 20:00:01 -0700, acss wrote:

Thanks John,

I have to enter the same invoice number several times and though it is from
the same customer ID it is from a different country. The current design that
i now have is Invoice Table, Invoice Details Table and Customers Table. I
have tried using autonumber as the primary key between Invoice and Details
table yet i need it to be one to many and not one to one. Is there a way to
have duplicates in the invoice table or a certain type of relationship to
accept duplicates?

A primary key applies to ONE table - your "primary key between" phrase is
meaningless. Your details table should have an InvoiceID - Long Integer if the
InvoiceID is an Autonumber - as a foreign key field, NOT its primary key; you
cannot (absolutely not!) use a link from an autonumber in one table to an
autonumber in another table.

A primary key HAS NO DUPLICATES. Period. End of story. That's it's definition:
a UNIQUE (only one!) identifier for a record.

If you want more than one record with the same invoice number then you cannot
use an autonumber and you cannot make it the primary key.

Autonumbers are generally not suitable for human consumption; they will always
have gaps, and auditors get really creepy when they see gaps in a run of
invoice numbers.
--

John W. Vinson [MVP]

Once again, use these tables ........
TblCountry
CountryID
Country

TblInvoice
InvoiceID (Autonumber)
CountryID
InvoiceNumber
etc

TblInvoiceDetail
InvoiceDetailID
InvoiceID (Number - Long Integer)
etc

InvoiceID is the primary key in TblInvoice. Invoices from different
countries are different invoices and as such each should be a separate
record in TblInvoice. Separate records means different InvoiceIDs. They may
have the same InvoiceNumber.

You should create a form/subform for data entry. The main form is based on
TblInvoice and the subform is based on TblInvoiceDetail. Notice that
InvoiceNumber is in the main form so when you enter a new invoice you only
need to enter the invoice number once. Likewise for country. You will be
able to enter multiple invoice detail records in the subform.

If you need help setting this up, I can help you. I provide help with Access
applications for a nominal fee. Contact me at

Steve

Steve



Steve


"acss" wrote in message
...
Thank you Steve. By making the design changes i continue to have a one to
one
relationship and not a one to many. I need to enter the same invoice
number
several times depending on 3 countries ex: 10234-china 10234-ecuador
10234.
The invoice 10234 is the same and from the same customer yet from a
different
country. The details on each invoice is what has the we need to capture so
having a one to one will not allow the details to change even though it is
a
different record. Is there anything i am missing here on what to do? It is
only two tables invoice and details.



"Steve" wrote:

Community Message Not Available

Thank you steve for the very detailed and supportive response. I fully
understand that what i am trying to do overides principles of access but that
is what i am delt with....a mandate to move from excel to access. I will work
on what you have generously provided and will contact you further if needed
on design.

Many thanks again

"Steve" wrote:

Once again, use these tables ........
TblCountry
CountryID
Country

TblInvoice
InvoiceID (Autonumber)
CountryID
InvoiceNumber
etc

TblInvoiceDetail
InvoiceDetailID
InvoiceID (Number - Long Integer)
etc

InvoiceID is the primary key in TblInvoice. Invoices from different
countries are different invoices and as such each should be a separate
record in TblInvoice. Separate records means different InvoiceIDs. They may
have the same InvoiceNumber.

You should create a form/subform for data entry. The main form is based on
TblInvoice and the subform is based on TblInvoiceDetail. Notice that
InvoiceNumber is in the main form so when you enter a new invoice you only
need to enter the invoice number once. Likewise for country. You will be
able to enter multiple invoice detail records in the subform.

If you need help setting this up, I can help you. I provide help with Access
applications for a nominal fee. Contact me at

Steve

Steve



Steve


"acss" wrote in message
...
Thank you Steve. By making the design changes i continue to have a one to
one
relationship and not a one to many. I need to enter the same invoice
number
several times depending on 3 countries ex: 10234-china 10234-ecuador
10234.
The invoice 10234 is the same and from the same customer yet from a
different
country. The details on each invoice is what has the we need to capture so
having a one to one will not allow the details to change even though it is
a
different record. Is there anything i am missing here on what to do? It is
only two tables invoice and details.



"Steve" wrote:

Community Message Not Available