Trying to solve results of Microsoft Access 2013 query selecting accumulated records

Operations.zipOperations.zipOperations.zipI am attaching a sample database with two queries and one table. One of the queries ("NotEqual_1") is trying to select records where the sum of "PctComplete" for specific operations <> 1. It is obvious when looking at operations for  "ABS_IP"  in the table that the sum of PctComplete values does = 1. The query shows that the results <>1. Any suggestions? Thanks!
Operations.zip
pabrannPresidentAsked:
Who is Participating?
 
Fabrice LambertFabrice LambertCommented:
Hi,

Comparition with floating point value always give headeach, this is due to floating point imprecisions.
See this article for details: https://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/

To solve your issue, you should modify your query a bit, by multiplying your results by 100, and checking against 100 (instead of 1).
You can use 1000 or higher value (depend on the precision you want).
After correction, your query can look like:
SELECT tblOperations.ProdClass,
              Sum(tblOperations.PctComplete) AS SumPctComplete
FROM tblOperations
GROUP BY tblOperations.ProdClass
HAVING Sum([PctComplete]*100) <> 100;

Open in new window

0
 
pabrannPresidentAuthor Commented:
Sorry, I didn't understand that the file was being uploaded and embedded, so I attached it multiple times. Logic here could be more intuitive.
0
 
Dale FyeCommented:
I cannot download your db at the moment.

Rather than testing for = 1, you might want to consider:

ABS([somevalue] - 1) < 0.00001

or somthing along those lines.  If you are using fields with double data type, there may be rounding errors when adding these numbers together.  When that occurs, you better off using the absolute value and subtracting 1 from your sum, and then comparing that value to some very small number.

HTH
Dale
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pabrannPresidentAuthor Commented:
Thanks Dale, I am more interested in testing for <> 1. The values for "ABS_IP" are 0.050, 0.100, 0.100, 0.150, 0.200, 0.100, 0.200, 0.100. They are not calculated numbers with long remainders. Running the query "select sum(pctcomplete) from tbloperations where prodclass = 'ABS_IP' results in "1".  However the query Criteria shows <>1 and ABS_IP shows up as values not totalling 1. I tried typing in your text above and it did not seem to work. The datatype is Double, format 0, Decimal Places 3.
0
 
pabrannPresidentAuthor Commented:
Fabrice, that works perfectly... Thank you very much!!!
0
 
pabrannPresidentAuthor Commented:
Thank you very much, perfect!!
0
 
Dale FyeCommented:
IF you are testing for <> 1 then, you would use:

HAVING ABS(SUM([PctComplete]) - 1) > 0.00001

Note that with Fabrice's solution you are multiplying a double precision number by 100, so you are still going to have a double precision number, with values which might vary from 100.0000002 down to whatever.  And because of rounding errors, 1.000002 * 100 is still not going to be equal to 100, even though that is what the sum of those values should be.  That is why, when working with double precision numbers, you should take the absolute value of the number minus the value you are testing against), and then compare that value to some extremely small value.

To test for X = 1 you would use:

ABS(X-1) < .0001

for x <> 1 you would use:

ABS(X-1) >= .0009

where .0001 and .0009 are determined by the degree of accuracy you are attempting to achieve.
1
 
pabrannPresidentAuthor Commented:
Dale, thank you very much.  I don't understand it but your solution works now and gives me the same result as Fabrice's solution. How can I give you points for this? I have never been confident that I know how to properly allocate points with this website.
0
 
Dale FyeCommented:
No need, I just wanted you to understand, in a little more detail, how to properly check for = or <> when dealing with double precision numbers.  It's never really as easy as when you are dealing with loop counters or something like that.

Dale
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.