Update future prices

[Sandy M]

I am a bit of a novice with Access but I need to implement a price change for
my business, going forward (saving all historical prices).
I use Access 07 to do my customer invoiceing
I have an hourly rate amount in a table (customer type) that I use as a
lookup in my customer table
The invoice amount for each customer is calculated in a query which has both
the customer and customer type tables (the calculation is simply hours times
rate)
I need to change the rate for some only customer types and would like to do
this only from today (ie keep historical rates )
Can this be done or do I have to just change the rate and live with the fact
it will report all past transactions at the higher rate and not the rate
actually charged?
Any help appreciated
Thanks

 

[Arvin Meyer [MVP]]

For historical data, you need to keep a history. That can be done in several
ways. First is that you can have a many side table that stores all the labor
rates then do complex querying to calculate each transaction. You can also
store the elements of each transaction. Or last, you can store the results
of the calculations.

The last way is the easiest, and less resource useful than the second (but
more than the first). It does break a normalization rule about storing the
result of calculations, but it is a valid reason.

The second way is a proper way to do it because besides being more
normalized, it can also possibly use the elements of the calculation for
further querying and reporting.

The first is the least space intensive, and the most normalized method, but
it is also the most difficult to use and will require lots of processor use.

I'd vote for the second method if there's any chance that you might be using
the data to determine things like rate increases over a period of time. I'd
break normalization, and use the third method if you'll never need that
data.

To use the second, just build a table to store each transaction including
all the elements but excluding any calculations.