query using aggregate sum function not sorting correctly

[LDS]

I’m using Access97. I have a report based on 2 queries. The first query uses
the aggregate sum function to total each employee’s overtime hours (General
number, 2 decimals). The query groups by social security number and sums
overtime hours. The second query sorts the overtime hours total in ascending
order, then sorts by the employee’s seniority ranking (in ascending order)
pulled from another table. All but 3 records sort correctly. There are 15
records having 6.61 as their total overtime hours. The first 3 seem to be in
a group of their own. They are sorted by seniority ranking. The next group,
also with 6.61 as their total, are sorted by their seniority ranking. I don’t
see anything wrong with their seniority ranking field. It’s just a number.
The overtime hours totals are all positive numbers although the sum query
adds positive and negative numbers. If the employee had no overtime there’s
a record with 0 hours in it. The totals are correct. They’re just not in the
correct seniority ranking order. The really strange thing is I took the
first record out of order and increased the number of records summed but kept
it so that it still had a total of 6.61. The record moved to the correct
position in the second query! That doesn’t make sense to me. Does anyone
have any idea what might be going on? I’m out of ideas. I need help right
away. Thanks for any suggestions.

 

[Marshall Barton]

I’m using Access97. I have a report based on 2 queries. The first query uses
the aggregate sum function to total each employee’s overtime hours (General
number, 2 decimals). The query groups by social security number and sums
overtime hours. The second query sorts the overtime hours total in ascending
order, then sorts by the employee’s seniority ranking (in ascending order)
pulled from another table. All but 3 records sort correctly. There are 15
records having 6.61 as their total overtime hours. The first 3 seem to be in
a group of their own. They are sorted by seniority ranking. The next group,
also with 6.61 as their total, are sorted by their seniority ranking. I don’t
see anything wrong with their seniority ranking field. It’s just a number.
The overtime hours totals are all positive numbers although the sum query
adds positive and negative numbers. If the employee had no overtime there’s
a record with 0 hours in it. The totals are correct. They’re just not in the
correct seniority ranking order. The really strange thing is I took the
first record out of order and increased the number of records summed but kept
it so that it still had a total of 6.61. The record moved to the correct
position in the second query! That doesn’t make sense to me. Does anyone
have any idea what might be going on? I’m out of ideas. I need help right
away.

Most likely the numbers are not exactly 6.61, they just
happen to be rounded to that for display purposes. Take a
look at the raw data to see if some of them might be
something such as 6.6099999 or 6.6100002

 

[LDS]

The overtime hours are only entered with 2 decimal places and I changed their
format to Fixed with 2 decimal places in the table itself and the queries.
They're still sorting out of order. The first person only has 3 entries: 2.5,
2.0, and 2.11 hours. The odd part is that if I change the entries to 4.11 and
2.5, using 2 entries instead of 3, the sort works. It doesn't make sense but
it works. I have to use the original entries. Hope you have another idea.
Thanks.

 

[Marshall Barton]

That's not at all strange, it's an ordinary consequence of
using a floating point representation of your numbers - they
are often inexact in some very small way. It's the
identical problem you would have trying to write down the
exact value of 1/3 on a piece of paper.

The only fixed point data type is Currency (4 decimal
places). If you can change the field to Currency type it
should get rid of the problem.

If you must use a floating point number, then try fudging
the values a little by using Round([overtime], 2) in the
record source query.

 

[LDS]

It worked! I changed the data type to currency with 4 decimals and it sorted
fine. Thank you so much. I never would have figured that out.

That's not at all strange, it's an ordinary consequence of
using a floating point representation of your numbers - they
are often inexact in some very small way. It's the
identical problem you would have trying to write down the
exact value of 1/3 on a piece of paper.

The only fixed point data type is Currency (4 decimal
places). If you can change the field to Currency type it
should get rid of the problem.

If you must use a floating point number, then try fudging
the values a little by using Round([overtime], 2) in the
record source query.
--
Marsh
MVP [MS Access]

The overtime hours are only entered with 2 decimal places and I changed their
format to Fixed with 2 decimal places in the table itself and the queries.
They're still sorting out of order. The first person only has 3 entries: 2.5,
2.0, and 2.11 hours. The odd part is that if I change the entries to 4.11 and
2.5, using 2 entries instead of 3, the sort works. It doesn't make sense but
it works. I have to use the original entries. Hope you have another idea.