Calculation in query or table

[soulice]

I have a db that stores a list of monetary entries. Each time and entry is made, a tax form is printed that shows a number for a credit based on the monetary value (45% of the monetary field value base on a year that runs from July to July). This form is a report based on a query. I understand best practice is to do it this way.
My issue is that sometime next year (or a few years down the road) a request may be made to print a copy of that form for a user. The percentage can change each year. I thought about either calculating it and storing it with the record (since it should never change for that year) thus it would always be available, or put together a table with a start and stop day for a range and number field for the percentage, then calc from that at report generation time based on the monetary entry date and find the percentage based on where the date rage fell. I think the "store it in the table" method may make sense this time. Thoughts?

 

[bob fitz]

I would save the original monetry value to which the percentage is applied and I would save the percentage. I would NOT save the actual calculated result (say 45% of £100 = £45). I would do the actual calculation as and when needed.

 

[plog]

To expand on bob's idea, you should save the percentage in a new table along with the timeframe it is valid for. Then whenever you need to calculate the result, you link that table into your query and use the percentage that way.

If a percentage is valid from Jan 1 to Dec 31 you only need to store the year in the new table, if it can be more than specific than that you would need to store the start date and end date the percentage is valid for.