Link to home
Start Free TrialLog in
Avatar of valkyri
valkyri

asked on

Incorrect Sum in Access Query


I have a simple query which I would like to sum the amount. The Select query (in SQL view) without the sum is:

SELECT Line_Items.IO, Line_Items.Amount, Line_Items.Date
FROM Line_Items
WHERE (((Line_Items.Date)=#4/3/2006#));

It gives these results:

Code      Amount      Date
1237540      -1126.02      2006-04-03
1237540      -2375      2006-04-03
1237540      -1885.5      2006-04-03
1237540      1126.02      2006-04-03
1237540      2375      2006-04-03
1237540      1885.5      2006-04-03
1728684      -1126.02      2006-04-03
1728684      -2375      2006-04-03
1728684      -1885.5      2006-04-03
1728684      2375      2006-04-03
1728684      1126.02      2006-04-03
1728684      1885.5      2006-04-03

I want to sum the amount, grouped by the Code.

The sql is:

SELECT Line_Items.IO, Sum(Line_Items.Amount) AS SumOfAmount, Line_Items.Date
FROM Line_Items
GROUP BY Line_Items.IO, Line_Items.Date
HAVING (((Line_Items.Date)=#4/3/2006#));

The results are:

IO      SumOfAmount      Date
1237540      0                                      2006-04-03
1728684      -4.54747350886464E-13      2006-04-03

As you can see, the sum for Code 1237540 is correct, while the sum for Code 1728684 is not correct, even though they contain identical amounts.

Why?
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Access is expanding your double value, it does this, and because its greater than 15, it puts in the -13
This problem occurs with VBA coding as well, not sure how and why Access calculates to so many decimal places, probably due to length of datatype double
Avatar of valkyri
valkyri

ASKER


I must be losing my mind, I've had this problem before. Oh well, thank you for reminding me.
No probs
This issue annoys me also