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
DatabasesMicrosoft Access
Last Comment
Dale Fye
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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