dividing currency with inaccurate results

[troublecat12]

I need to divide an amount of money into 3 or 4 payments, but when my
original amount isn't evenly divisible, my calculation rounds the decimals
all the same way so that the total of the payments does not equal the
original amount (off by a cent). Is there any way to correct this?

i.e. $400 divided into 3 payments of $133.33 each, totaling $399.99.

 

[Michel Walsh]

None that I know short of making n-1 rows with the rounded amount, sum these
rows, sum you will subtract from the original amount, giving the last
payment to append as final row.

Alternatively, make n rows with the rounded amount, get the rounding error,
add it to the first (last) row you just generated.



Hoping it may help,
Vanderghast, Access MVP

 

[S.Clark]

When Microsoft Access Math Doesn't Add Up by Luke Chung
http://fmsinc.com/tpapers/math/index.html

 

[John Spencer]

If Amount is a field of type currency, then could try

SELECT Round(Amount/NPayments,2) as NMinusOnePayments
, Amount-Round(Amount/NPayments,2) * (NPayments-1) as LastPayment
FROM SomeTable


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================