relationship nightmare!!

[tx sales]

Trying to set up my invoice database to automatically enter product name and
price according to the item number I enter.

Can anyone help me?

 

[Damian S]

Hi tx sales,

Generally to link two tables together, you would do something like this. In
your InvoiceDetails table you would have a field called something like
ItemNumber that is linked to the ItemNumber field in the Items table. Use a
Combo Box to select an Item, using me.COMBOBOX.column(n) to grab details out
of the column, or use a text box to key in the item number and use dlookup to
lookup the item from the Items table.

It all depends on whether you are having a straight link between the two
tables, or if you are wanting to capture a snapshot of the data as it stands
at the time of doing the invoice. If you want a snapshot, you will need to
have columns to record the ItemNumber, ItemName and ItemPrice in your
InvoiceDetails table, with no link to the Items table. This gives you the
ability to record the Item Description etc at the time of the sale. If you
have a link, you will update the price of ALL INVOICES that used the Item you
change the price on, which isn't what you want for an invoice.

You might want to use a combination of the two methods. Have a link to the
Items table (as a widget is a widget even if it's name changes), but have a
recorded price in the ItemDetails table, that way you could offer a discount
to some customers etc.

Hope this rambling makes sense to you.

Damian.

 

[John W. Vinson]

Trying to set up my invoice database to automatically enter product name and
price according to the item number I enter.

Can anyone help me?

I'll suggest that you "unask" the question.

Relational databases use the Grandmother's Pantry Principle: "A place
- ONE place! - for everything, everything in its place".

The product name - certainly - should be stored in the Product table,
ONLY. It should simply *not exist* in the Invoice table or the
InvoiceDetails table (which I presume you have); *only* the ProductID
should be stored.

The price is a different issue, since you'll typically want to record
the price of an item *as of the time that the invoice was created*; if
the price subsequently changes, you don't want the older invoices to
change their price.

For an example of how this can be done, see the Orders form in the
Northwind sample database. Look at the Product combo box on the
OrderDetails subform.

John W. Vinson [MVP]