Cannot get database SUM to add correctly.

Question

I have Microsoft Office Database 2007. I cannot get the sum function to add correctly all the time. It will be a couple pennies out every so often and cannot figure out why. It does not add correctly when I have a number like 125.605. It will not round up. So I changed all the fields to 4 decimals. but it still won't add the third decimal.

Answer 1

Oh dear... sorry I don't use Works so I can offer no solution.  I did find this...

http://support.microsoft.com/kb/76290

Answer 2

OK CORRECTION I'm using Microsoft Works Database! Thought it was an office program.

Apparently it will not accept any additional function like round etc.

what my database function is:  weight * price = net

                                                1250 * 0.6575 = $821.88

When it goes to the report it has to add say 50 of these together. So it's the half and quarter cents throwing it out.

My function is just =SUM(NET)

It rounds properly when it is in the actual database. It just does not want to add the cents properly in the report.

Answer 3

Hmmm, have a look at...

http://allenbrowne.com/round.html

Answer 4

If you are using the Sum fubction in a Totals type query or in a form/report header/footer section, then the problem is probably because you are manually adding up the individual displayed values, which are rounded, and comparing that to the totalm which is not rounded until after the total is calculated.  In this case the total is the most accurate value and your manual calculation can be off by quite a bit.

OTOH, if you are writing checks then the individual rounded values is what you write on the check and the total need to be a sum of the rounded values.  That means instead of using an expression like  =Sum(Amount)  you need to use  =Sum(Rnd(Amount, 2))

If you are using Single or Double type number, but only need 4 decimal places, change to Curreny data type.

If that diesn't help, post a Copy/Paste of how you are summing the field and provide more details about how the numbers are off.