AutoNumber Question

[jk]

I would like to design a DB which would track the expenses of 40 customers
..The problem is i need to understand the concept of using the autonumber or
the actual 5 number account as the primary key in the customer table. The
customers would be billed monthly so the account would be repeated in the
field each time a new invoice is entered. Is this correct since primary keys
do not allow duplicates?

 

[jk]

I have unique invoice numbers and customer accounts. The customers are billed
monthly and they use expense accounts for the charges of the invoice. I
downloaded the template MS ACCESS expense DB to get a reference but the
design uses autonumber for each table. Utlimately, i want to be able to track
the expenses for each customer account but did not know if autonumber is the
most efficient.

 

[John W. Vinson]

I would like to design a DB which would track the expenses of 40 customers
.The problem is i need to understand the concept of using the autonumber or
the actual 5 number account as the primary key in the customer table. The
customers would be billed monthly so the account would be repeated in the
field each time a new invoice is entered. Is this correct since primary keys
do not allow duplicates?

You certainly would NOT duplicate the account information with each
bill! You have a One (account, customer) to Many (invoices, bills)
relationship.

Since you have - apparently - a five-digit, unique, unambiguous
account number, you should just use that as the Primary Key of the
Accounts table. Each account will be represented only once, so you
don't need to worry about duplicates.

Your separarate Invoices table would have the Account Number as a
(non-unique, non-primary) foreign key field, so you can put in as many
invoices as are needed for each account.

The Invoices table will need a Primary Key; typically this would be an
Invoice Number. I would not recommend an Autonumber for this either,
since autonumbers can and will have gaps, and accountants get really
creeped out by missing numbers in such sequences; instead, consider a
manually or programmatically incremented Integer number field.

John W. Vinson [MVP]

 

[jk]

Thanks for the info. I am looking at about three
tables---customers-invoices-expense accounts. What i am trying to grasp is
creating the data entry form so the user keys in the fields for date-invoice
number-invoice amount-customer name- and then the customer account. This
entry form would be used monthly so wont the customer account field be
repeated on each entry each month?

 

[John W. Vinson]

Thanks for the info. I am looking at about three
tables---customers-invoices-expense accounts. What i am trying to grasp is
creating the data entry form so the user keys in the fields for date-invoice
number-invoice amount-customer name- and then the customer account. This
entry form would be used monthly so wont the customer account field be
repeated on each entry each month?

How are your tables related? Does each Customer have multiple
Invoices? How are Expense Accounts related to the other two entities?

I'm sure you'ld use a Form based on the "one" side of a relationship
and a Subform based on the "many", and you certainly shouldn't repeat
any customer DATA - though you will need a foreign key (perhaps the
customer account number, since I don't know the structure of your
tables) repeated; that's typical of foreign keys and is not a flaw.

John W. Vinson [MVP]

 

[jk]

Thanks John. The customers will have mulitple invoices and expense acct will
be applied on each invoice. I have moved away from autonumber and into the
unique numbers for account and invoice. The form with subform does what i
need and i will need to make the design of it a little more user friendly but
you have been a great help!