?
Solved

MS Access 2003 - sum is producing too many decimal places

Posted on 2009-04-21
6
Medium Priority
?
760 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;

Open in new window

0
Comment
Question by:Muskie12
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24197629
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

by:Muskie12
ID: 24197751
This changes the field to TEXT format though...
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24197825
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Muskie12
ID: 24198360
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

by:rockiroads
ID: 24198550
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

by:Muskie12
ID: 24199150
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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