Keeping field contents if a source changes

[gafferuk]

I am having a little issue that I'm hoping somebody can help me with. I am writing a database for estimating contracts in the roofing industry. I want to set up a table of materials with prices next to them, not a problem. When I create a form for estimates and I select a material from a drop down list it will use the current price for the selected material. My problem is if I update the price of the materials it will change across all my estimates which is not what I want, I would only want new estimates to have the new price and old estimates to have the price I had at the time. Is there a way of freezing the field content so that it does not update when the price updates?

[RuralGuy]

This is one of those situations where you want the value in more than one table for historical reasons. Your estimate table should have a field for the price as well as the material and you should copy the price to the table instead of doing a lookup each time.

[gafferuk]

yes sorry you are correct I don't need to do a lookup for the price, what I would do is a lookup of the material and then the price would be copied across. However if i do an estimate and send that quote out and the price for a material changes which i would change in the prices table how would i stop that price copying over old prices on the estimates table. i don't want old estimates to update when material prices change

[RuralGuy]

Only your form would set the price value in the estimate table, right? I don't see the problem.