M
Michel Walsh
That may be due to the overall procedure used.
As example, if the unit price is 1000.01 and the tax rate is 1%:
1000.01 ' price
+ 10.0001 ' tax, full precision
==============
1010.0101 ' total, full precision
Now, for a quantity of 100
- if we round right now before multiplying by the quantity, that makes
1010.01 then, next, multiplying by 100, that ends up with 101 001.00
- instead, if we first multiply by the quantity and only round later:
1010.0101 * 100 = 101001.01 and now, rounding, we end with 101 001.01
so, one penny of difference.
The first formulation (assuming everyone is a decimal data type, for
simplicity) is what we get with:
SUM( quantity * INT(0.5+ unitPrice*taxRate*100))/100
the second formulation is what we get with:
SUM( INT(0.5 + quantity * unitPrice * taxRate ))/100
Since your queries were all like the second formulation, I can only guess
that you compare that result with a procedure which is equivalent to the
first formulation.
Technically, I suppose that the first formulation is what should be use, in
reality. So, it would be to try to rewrite your queries from their actual
formulation, into the first formulation, ie, move the quantity outside the
INT( ) rounding.
Vanderghast, Access MVP
As example, if the unit price is 1000.01 and the tax rate is 1%:
1000.01 ' price
+ 10.0001 ' tax, full precision
==============
1010.0101 ' total, full precision
Now, for a quantity of 100
- if we round right now before multiplying by the quantity, that makes
1010.01 then, next, multiplying by 100, that ends up with 101 001.00
- instead, if we first multiply by the quantity and only round later:
1010.0101 * 100 = 101001.01 and now, rounding, we end with 101 001.01
so, one penny of difference.
The first formulation (assuming everyone is a decimal data type, for
simplicity) is what we get with:
SUM( quantity * INT(0.5+ unitPrice*taxRate*100))/100
the second formulation is what we get with:
SUM( INT(0.5 + quantity * unitPrice * taxRate ))/100
Since your queries were all like the second formulation, I can only guess
that you compare that result with a procedure which is equivalent to the
first formulation.
Technically, I suppose that the first formulation is what should be use, in
reality. So, it would be to try to rewrite your queries from their actual
formulation, into the first formulation, ie, move the quantity outside the
INT( ) rounding.
Vanderghast, Access MVP