Solved

MS Access 2003 - sum is producing too many decimal places

Posted on 2011-03-14
6
677 Views
Last Modified: 2012-05-11
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...
0
Comment
Question by:gpdixit
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35128633
try this

SELECT tblDrafts.D_CaseNumber, formatnumber(Sum(tblDrafts.D_Amount),2) AS SumOfD_Amount
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 35128832
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35129558
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 35129815
I was wondering when your "Spidey sense" would start tingling, gustav :)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35129915
Believe me, it's operating at random!

/gustav
0
 

Author Closing Comment

by:gpdixit
ID: 35153893
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now