Solved

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

Posted on 2009-04-21
729 Views
Last Modified: 2012-05-06
I have a table that contains a field which I have manually set to Number, Fixed, 2 Decimal places (to try and resolve this problem) yet when I do the following query on it, some records that are sum'd produce a value that has 8 decimal places or more or a suffix (i.e. SumAmount
-7.27595761418343E-12). It would seem that one or more of certain records have an amount that has multiple decimal places (even though they don't appear to when simply viewing the table.

I need the amount in Table2 to be truly 2 decimal places as I need to compare this field value with another field in a different table and make a decision based on whether the values are equal or not.
``````Query:

SELECT Table1.Control, Sum(Table1.Amount) AS SumAmount INTO Table2

FROM Table1

GROUP BY Table1.Control;
``````
0
Question by:Muskie12
6 Comments

LVL 65

Expert Comment

use the format comand like this

Query:
SELECT Table1.Control, Format(Sum(Table1.Amount),"#.00") AS SumAmount INTO Table2
FROM Table1
GROUP BY Table1.Control;
0

Author Comment

This changes the field to TEXT format though...
0

LVL 65

Accepted Solution

Well how about using the ROUND function then? It will return the number rounded to the number of decimal places specified

Query:
SELECT Table1.Control, Round(Sum(Table1.Amount),2) AS SumAmount INTO Table2
FROM Table1
GROUP BY Table1.Control;
0

Author Comment

Although the round function doesn't give me the 2 decimal places (if they are "00") it does indeed keep the value in number format and does maintain 2 decimal places when they are non-zero values, so you get the points.

I still have the problem though, that for only some records, when I go to compare this value (that I rounded off) with the value in another field, the compare fails which makes it seem that the rounded-off value is not the same as the other value - even though both values appear to be the same when displaying the table.

I will try to apply the round function to the other value before doing my compare, but I don't hold out much hope   :-}
0

LVL 65

Expert Comment

This is the problem with rounding. If your other value is in 2 decimal places only then would the format command not work as you are in effect comparing two strings?
0

Author Comment

Thanks rockiroads.

It was only by applying the 'round' function to *both* cost and amount fields when creating the two tables that my (WHERE) compare worked perfectly and the query below would display only those records where there was a legitimate difference between the two values (I guess I should have been more optimistic!).

SELECT StockCosts.Stock, StockCosts.SumCost, StockSums.SumAmount
FROM StockCosts INNER JOIN StockSums ON StockCosts.Stock=StockSums.Control
WHERE StockCosts.SumCost<>StockSums.SumAmount;
0

## Join & Write a Comment Already a member? Login.

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!