Using a calculated field in a table, but I need it to do 2 different calculations

[MANDYE]

Hi,
I have a table that details all of the sales information daily for several different restaurants. I used a "Calculated_Tax" field to indicate the amount of sales tax to be collected, the only problem is that in July 2015, our county increased the sales tax percentage. If I updated the field to the new sales tax amount, all of my previous data shows big sales tax shortages.
Can I separate the tables by date, correct the calculated field in each, and then link them so that my queries will pull data from both tables?
Suggestions?


Thank you so much!

[ranman256]

This is why you must have a tTaxRate table, to store the changing rates.
the tax rate is then added to the table record as a value for that date.
THIS is used as a calc.

you don't separate data tables by date.

[CJ_London]

It's also why you should not use a calculated field (which can only reference data within that record) - although in this instance I would recommend you do need a fields to put the calculated value for all time (one each for percentage and calculated value) - you would manage this in your form for creating invoices.