MS Access 2003 - sum is producing too many decimal places

SELECT tblDrafts.D_CaseNumber, Sum(tblDrafts.D_Amount) AS SumOfD_Amount
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;

produces sum with many decimal digits instead of two digits as defined in the table. By using Round function, it is not giving correct sum in decimal values. Example, the value in table is say 169531.10 and it is showing 169531.09 because it is rounding 169531.09354...
gpdixitAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
gpdixit,

Unless you are using the Decimal data type for that column with a Scale parameter of two, then you are NOT actually storing your values as numbers with two decimal places--rather, they are merely formatted that way for display.  As such, cap1's suggestion will not necessarily return the correct answer.

Instead, you would have to round each value BEFORE it goes into the sum:

SELECT D_CaseNumber, Sum(Round([D_Amount], 2)) AS SumOfD_Amount
FROM tblDrafts
GROUP BY D_CaseNumber;

Open in new window


That, of course, uses "banker's rounding".  To round Excel-style:

SELECT D_CaseNumber, Sum(Val(Format([D_Amount], "0.00"))) AS SumOfD_Amount
FROM tblDrafts
GROUP BY D_CaseNumber;

Open in new window


Be warned, though, that taking the sum of rounded items is a mathematically dubious idea :)

Patrick
0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT tblDrafts.D_CaseNumber, formatnumber(Sum(tblDrafts.D_Amount),2) AS SumOfD_Amount
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;
0
 
Gustav BrockCIOCommented:
Always - always - use data type Currency for currency, amounts, etc.

If you can 't change the fields of the table, convert to Currency at the earliest stage:
SELECT 
  tblDrafts.D_CaseNumber, 
  Sum(CCur(tblDrafts.D_Amount)) AS SumOfD_Amount
FROM 
  tblDrafts
GROUP BY 
  tblDrafts.D_CaseNumber;

Open in new window


The reason for your rounding errors is caused by adding positive and negative amounts when these are Single or Double.

/gustav
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Patrick MatthewsCommented:
I was wondering when your "Spidey sense" would start tingling, gustav :)
0
 
Gustav BrockCIOCommented:
Believe me, it's operating at random!

/gustav
0
 
gpdixitAuthor Commented:
Expert gave two solutions, out of which second solution was acceptable. I tried and and it is successful. Solution was provided in a very short time. Lot of thanks.
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.