Solved

# MS Access 2003 - sum is producing too many decimal places

Posted on 2011-03-14
Medium Priority
825 Views
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
Question by:gpdixit
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 120

Expert Comment

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 93

Accepted Solution

Patrick Matthews earned 2000 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;
``````

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;
``````

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

Patrick
0

LVL 52

Expert Comment

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;
``````

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

/gustav
0

LVL 93

Expert Comment

ID: 35129815
I was wondering when your "Spidey sense" would start tingling, gustav :)
0

LVL 52

Expert Comment

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

/gustav
0

Author Closing Comment

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
###### Suggested Courses
Course of the Month15 days, 3 hours left to enroll