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?
valkyriAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
Would u be happy if the value was rounded?

SELECT Line_Items.IO, Round(Sum(Line_Items.Amount),2) AS SumOfAmount, Line_Items.Date
FROM Line_Items
GROUP BY Line_Items.IO, Line_Items.Date
HAVING (((Line_Items.Date)=#4/3/2006#));
0
 
rockiroadsCommented:
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
0
 
valkyriAuthor Commented:

I must be losing my mind, I've had this problem before. Oh well, thank you for reminding me.
0
 
rockiroadsCommented:
No probs
This issue annoys me also
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.