"David W. Fenton" <dX********@bwa y.net> wrote in message
news:93******** *************@2 4.168.128.78...
In regard to tax, I think it's always a good idea to store the tax
amount in the record, as opposed to the tax *rate*, because when
you calculate the tax amount from the rate, you may have rounding
errors that will have to be addressed every time you calculate the
total. If you store the tax *amount*, you do the rounding only
once.
I've always store the amount rather than the rate, and for the same reasons.
Recently I've begun to question whether this is still justified.
Strictly, we are denomalizing when we store the amount of tax, as it is
dependant on the amount of the transaction. As processor power increases,
the number of cases where denormalization makes sense decreases. What we did
in dBase III on a PC with no hard disk and 128*K* of RAM is not always
appropriate today.
Storing the tax rate and calcuating on the fly means:
1. Store the rate per row, since countries have specific items that are
tax-ex.
2. Use a query as the source for forms/reports, to include the calculated
field (tax amount).
3. The calculated field must be explicitly typecast to Currency. (*No*
calculated field can be trusted without typecasting.)
4. Currency cannot be Null, so the calcuation must involve Nz().
5. The row must be rounded to prevent apparent addition errors if the client
wants to see the tax-inc amount on each row.
Result:
TaxAmount: Round(CCur(Nz([Quantity] * [UnitPrice] * [TaxRate],0)), 2)
The question is whether processor speed now justifies performing such a
calcuation at every row of the invoice in preference to storing the
denomalized tax amount. Guess we should run some timing trials to find out.
Variables would include processor type/speed, local verses networked, JET
verses MSDE.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html