Link to home
Create AccountLog in
Avatar of Phil
PhilFlag for United States of America

asked on

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
Avatar of Phil
Phil
Flag of United States of America image

ASKER

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.
Avatar of Dale Fye
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
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Phil

ASKER

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.
Avatar of Phil

ASKER

Fabrice, that works perfectly... Thank you very much!!!
Avatar of Phil

ASKER

Thank you very much, perfect!!
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.
Avatar of Phil

ASKER

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