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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 FyeOwner, Developing Solutions LLCCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.