Bug

[Guest]

One of our users reported this:

This is a sample of my database that illustrates the bug I found.
My query is searching for all records whose sum of the percentage is not
equal to 100% (1). It looks like this, very simple, but result show some
records with 100% (1) which is incorrect.

SELECT tblCEMActivityAllocation.ActivityID,
tblCEMActivityAllocation.SubActivityID,
Sum(tblCEMActivityAllocation.Percentage) AS SumOfPercentage FROM
tblCEMActivityAllocation GROUP BY tblCEMActivityAllocation.ActivityID,
tblCEMActivityAllocation.SubActivityID
HAVING (((Sum(tblCEMActivityAllocation.Percentage))<>1));

The only way I made it work was by converting the sum of the percentage to a
string and compare it's result to a string value of "1", that gives me a
correct result, but arithmetically is not right.

SELECT tblCEMActivityAllocation.ActivityID,
tblCEMActivityAllocation.SubActivityID,
Sum(tblCEMActivityAllocation.Percentage) AS SumOfPercnt FROM
tblCEMActivityAllocation GROUP BY tblCEMActivityAllocation.ActivityID,
tblCEMActivityAllocation.SubActivityID
HAVING (((CStr(Sum([tblCEMActivityAllocation].[Percentage])))<>"1"));

Any comments?
Thanks.

 

[John Spencer]

Probably a floating point number problem. The Sum is probably very close to
1 but not exactly 1 due to the use of floating point arithmetic. Try
solving it by using

HAVING Abs(Sum(tblCEMActivityAllocation.Percentage) -1) < .000000001

Floating point numbers (single and double number types) can not accurately
represent fractional numbers. It is the equivalents as trying to accurately
write the value for 1/3 as .333333333333333333333333333333333333... You can
come close but can't ever get the exact equivalent. This becomes especially
noticeable when you start doing math operations with floatring point numbers
that involve a decimal portion on the numbers.

Another way to handle this would be to use an expression like the following.

HAVING CLng(Sum(tblCEMActivityAllocation.Percentage) * 100) = 100

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..