Rounding Issue - Unexpected Results 1

[accessjack]

I have table (tblSales) with 3 fields: product, department and percent. The percent field is "double" number format.

Sample Data
Product Dept Percent
A 1 .308
B 1 .506
C 1 .186

Each Department should add up to 1 (or 100%). I must load this data into an oracle table (which cannot be modified), but it can only accept 2 decimals. I use the round function which converts product a b & c percents to .31, .51, and .19 respectively. I then create a query based off of tblSales grouping by department and summing by percent. This results in some departments being slightly more or less than 1 due to rounding.

The issue is I am trying to filter out departments whose totals are equal to one, by writing "<>1" in the criteria box on the query below Percent. The weird thing is several departments whose totals are equal to 1 still show up in this query, even though I can clearly see the results are precisely equal to 1.

Does anyone know why I cannot restrict my query results to only department whose percent totals do not equal 1, the ones whose percents need to be adjusted due to rounding.

Thanks,
AccessJack

 

[PHV]

This is a common problem with double as floating point numbers are just an approximation.
Replace this:
HAVING Sum(Percent)<>1
with this:
HAVING Format(Sum(Percent),'0.00')<>'1.00'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[accessjack]

Thanks PHV, that did the trick.

Also, I notices that if I used "Not Like(1)" it also worked.