Keeping old data in a lookup

[Guest]

I've got a question about preserving existing data when a lookup value
changes. If I have a Product table which includes Product ID, Product
Description and Cost fields. This table is used as a lookup in an Order
Details table. My question is how I can keep existing records in the Order
Details from changing when I change the Cost field. In other words I want to
keep the old cost value when looking at old (pre cost change) records, but
use the new Cost values in current records. I'm not even sure what this would
be called so I can search for an answer. Thanks in advance for any insight.

JMR

 

[Allen Browne]

You need to add a CostEach field to your OrderDetail table, so it records
the cost at the time of the order.

This is not redundant data, for exactly the reason you indicated:
- Product.Cost is the current usual cost of the product
- OrderDetail.CostEach is the actual charge to that client at that time.

After adding the new field to your OrderDetail table, use an Update query to
populate your exising records with the old cost amount for each product.

Use the AfterUpdate event procedure of the ProductID (combo?) in your
OrderDetail subform to lookup the Cost and assign it to the CostEach.
There's an example of this in the Northwind sample database that installs
with Access, in the Order Details subform.

 

[Guest]

Thank you Allen, that is exactly what I was looking for and the Northwind
example was spot on.

JMR