Calculations in queries

[Bentleybelle]

I have a database that is being used for sales orders. A form is used to enter customer info, then a subform for order lines, which has a product number, description, qty and unit price. Am I right in thinking that a discounted prices needs to be calculated in a query, another calculation then for the total price, and the subform based on the query? If this is the case, what happens when product prices are increased? Won't this alter pricing of past orders? How then, should the unit price be stored?

I'm fairly new to (what seems to me!) complex stuff, so any help would be appreciated.

Thanks in anticipation.

 

[neileg]

You should have a table that stores the product ID and the current price. You will have a table for order lines that will be populated with the price at the time the data is saved. Unless you specifically update that price, the value will stay the same, and the calculation will always return the same total price.

If you are applying discounts, you need to record the relevant discount, too, incase the discounts change over time, as well.