Question How to prevent updating existing values in a query field

[Nilay]

Detailed question didn't fit in the title : How to prevent updating existing values in a query field when the value in the related table field changes.

I have a table Product, which has fields product number, description, price, etc. I then have a query ProductQ, which has all the fields of the table Product, and some calculated fields (like tax from %, total price) based on the Product fields. This query ProductQ is used in other queries for making Purchase orders, quotation and invoices.

I have been using this structure since last one year. Now some prices have been revised, but what happens is if I edit the price in the table Product, then all the existing records in Purchase Orders and Invoices reflect the edited price. I want to change the price of only future records, not the existing ones. How do I do it ?

There are no relationships as such, as there is only one table which has the price and that is Product table.

My present option is to duplicate all product records except the product number and then change the prices in the duplicated records and start using them instead of the old records, but I would really like to have a permanent solution, as the price change will be very frequent. Thanks for these forums

 

[Rabbie]

I have found the simplest way is to store e the actual price used in the record that describes the sale.

In my case I have tables tblPrices, tblOrder and tblOrderDetail. The information for each item I get from tblPrices but I copy the price from tblPrices to tblOrderDetail. This means that any future change to tblPrices will not change the historic data so I can always look at an old Order and see how much it was for.

 

[Nilay]

I too have a similar table structure. I have tblCustOrder (which has order number and terms) and tblCustOrderProduct (which has products and quantity, and joins the previous table with common field OrderNumber). Difference is my order details, like total price (Quantity from tblCustOrderProduct multiplied by Price from query ProductQ), total tax (Qantity from tblCustOrderProduct multiplied by Tax from query ProductQ), etc are stored in a query. If I get you correctly, I need to store these in a table. How do I do that ? tblCustOrderProduct does have the fields like TotalPrice and TotalTax, but they are empty as I don't know how to store a calculated value in a query to a table. Thanks for help