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/2
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/2
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
This issue annoys me also
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