Relationships

[Guest]

I am creating a database that is holding information about orders and
products we buy and prices. I have created some look up relationships to
look up data int he products table and store it in an orders table. How do I
store the look up unit price in the orders table without it changing when i
update the unit price field in the products table so that I have a history of
the prices I have paid in the past when the prices increase??

 

[chriske911]

It happens that jo formulated :

I am creating a database that is holding information about orders and
products we buy and prices. I have created some look up relationships to
look up data int he products table and store it in an orders table. How do I
store the look up unit price in the orders table without it changing when i
update the unit price field in the products table so that I have a history of
the prices I have paid in the past when the prices increase??

each time you create a new order you look up the product unit price
value
and store this as orderprice
this way the existing orders are not affected by a changed product unit
price

for a historical view you can always create a custom select query

grtz

 

[Wayne Morgan]

There are two ways of doing this.

1) Have a pricing table that has the ProductID, Price, and EffectiveDate.
You could always look up a price from this table based on the date. It will
also allow you to enter changes you know are coming before they actually go
into effect.

2) Include the price column in the combo box that is looking up the product.
In the AfterUpdate event of the combo box, set the value of a textbox to the
price column of the combo box. The textbox would be bound to the price field
in the form's table and so the value will be stored when the record is
saved.