How do I set up a relationship between items with multiple entrie.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cwissle - 30 Nov 2004 15:03 GMT

I am trying to set up a database for capital and expense coding for invoices.
I need to key one invoice with multiple lines of capital and expense codes.
When I put the invoice number as the primary key, I am receiving errors and
can not run reports and queries.

Reply to this Message

Sprinks - 30 Nov 2004 15:49 GMT

Hi, Cwissle.

You will need two tables to accommodate the one-to-many relationship between
Invoices and "InvoiceEntries". Something like:

Invoice
------------
InvoiceNumber AutoNumber or Number (Primary Key)
InvoiceDate Date
Customer Number (Foreign Key to Customer table)
PaymentTerms Number (Foreign Key to Terms table)
Etc.

InvoiceEntry
-----------------
EntryKey AutoNumber (Primary Key)
InvoiceNumber Number (Foreign Key to Invoices)
EntryCode Number (Foreign Key to CapitalExpenseCodes)
Amount Currency

Use a combo box for the Customer and EntryCode foreign keys, a continuous
subform for the InvoiceEntry records, placed on a main form based on Invoice,
linked by the InvoiceNumber.

HTH
Sprinks

> I am trying to set up a database for capital and expense coding for invoices.
> I need to key one invoice with multiple lines of capital and expense codes.
> When I put the invoice number as the primary key, I am receiving errors and
> can not run reports and queries.

Reply to this Message