Phil

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

Operations.zip

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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.

ASKER

Fabrice, that works perfectly... Thank you very much!!!

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.

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.

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

Dale

ASKER