General Question

[cbuechner]

I am looking for a way to save data or even a report, that once written, wont change, even if the background data changes.

For example, the price of gas is 2$ per gallon. I calculate I need to get 5 gallons a week. I further calculate in a report, that I need to to spend $10.00 a week for gas. The price of gas changes in a month to $3.00 per gallon, and if I run the report, it will tell me based on the new $3.00 per gallon, and not have both.



Tables stay the same, however, the queries with calculations change based on the table value.

OR maybe I can have a field that if checked, wont allow changes?

This is a very simple look at what IM trying to do, but basically, I want to keep the final record, as a historical point.

[orange]

See this re AgreedTo Price

If you store the Product Price with the Product record, you will have difficulty when you change that Price--((all of your historical sales records will change)). To avoid this, you can put the CurrentPrice of the Product in the Product table, and put the ProductID, the AgreedUponPrice and the Quantity in the OrderDetail record.

AgreedUponPrice is the negotiated Price that you sold this product to this Customer in this Order and that price could include sale price/clearance item/loyalty reward program/special discount.

[ranman256]

you need a 'result' table that adds all the elements;

gasRatePerGal , #gals , Month
$2 , 10 , Jan-2021

this table is NOT in a relationship link. Its a historical table that all other tables add to , to 'freeze' the rates.

[CJ_London]

based on your example, another way is to store a history of the 'component elements' with an effective from date so for example.

tblPrices
PricePK
ItemFK
Price
DateFrom

So for any calculation requiring a price for an item on a specific date - you would look up the price effective on that date. - i.e. the record which has the highest DateFrom which is less than the date you are looking for.

Also has the benefit of forward modelling. If you know the price is going up next month, you can enter it now, not wait until it has happened. So a report for this month would show the price effective for this month, the same report run for next month would show next months price.

depending on complexity, you might also include a timestamp field which will then maintain when a record was changed. So if in the above example when you run next months report you haven't entered the new price, then you do enter it and run the report again, you have the option of two views, the first as the current view (based on datefrom) and the second as the view that was current at the time of the first time the report was run (based on timestamp)

However there are occasions where you might want to store values which are 'a matter of fact' particularly around invoicing.