Autonumber 0ne-One

[acss]

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

 

[Piet Linden]

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 primarykey
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.

 

[acss]

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???

 

[Steve]

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

 

[acss]

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.

 

[John W. Vinson]

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?

 

[acss]

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?

 

[John W. Vinson]

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.

 

[acss]

Thanks for the support.

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.

 

[Steve]

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

Steve

Steve
(e-mail address removed)


Steve
(e-mail address removed)

 

[acss]

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]

Moving from excel to access doesn't necessitate throwing basic principles of
relational database design out the window! When creating a database, start
with design of the tables. Keep forms and reports out of your mind. Once you
have normalized tables, there are always ways to dsign forms and reports.

Steve

 

[John... Visio MVP]

If you need help setting this up, I can help you. I provide help with
Access applications for a nominal fee. Contact me at (e-mail address removed)

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP

 

[John... Visio MVP]

If you need help setting this up, I can help you. I provide help with
Access applications for a nominal fee. Contact me at (e-mail address removed)

Steve

Steve
(e-mail address removed)

Stevie, changing your email address will not let you sneak under the radar.
Though the use of "nominal" and the double signature is a nice touch.

On another point, it is not very bright to continually flaunt the rules,
antagonize the users of these newsgroups and post personal information. Over
the years you have posted about your family and where you live. Luckily, the
people you have annoyed in these newsgroups have far more class than you to
actually use any of these tidbits against you.

John... Visio MVP

 

[John... Visio MVP]

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

Careful acss, stevie is our local troll who pretends to know what he is
talking about. His only interest is in making money.

These newsgroups are for FREE peer to peer help and there many here who
would gladly help for free.
Before you engage anyone, you should check out their references.

John... Visio MVP

 

[John W. Vinson]

Stevie, changing your email address will not let you sneak under the radar.
Though the use of "nominal" and the double signature is a nice touch.

On another point, it is not very bright to continually flaunt the rules,
antagonize the users of these newsgroups and post personal information. Over
the years you have posted about your family and where you live. Luckily, the
people you have annoyed in these newsgroups have far more class than you to
actually use any of these tidbits against you.

John... Visio MVP

Cool it, John, please. IMO you're crossing the line.

 

[acss]

John & John,
I appreciate the input and warning yet my answer was in a nature of being
thankful for a response from fellow posters and i take all response's as
ideas when working through a solution. I have always weighed responses from
MVP only to the greater extent for assistance and highest credability. I have
a design issue before me and will work it though and posting assitance has
always been the needed support in creating a useful DB. Once again thanks.

 

[John... Visio MVP]

Cool it, John, please. IMO you're crossing the line.

Sorry John, but I do not see it that way. I was just trying to make him
think.

John... Visio MVP

 

[Keith Wilby]

will contact you further if needed
on design.

Be sure to read this first:

http://home.tiscali.nl/arracom/whoissteve.html

 

[Keith Wilby]

I was just trying to make him think.

We all know that that's a complete waste of time