Totals query not grouping 1

[redwoodly]

Hi

For each day housekeeping works (TimeSheetID), they fill out a slip that tells start time and end time spent in each room. For each Room type (Queen, King, Suite, etc) we have a target time that they are supposed to shoot for. Queens and Kings may be different room types, but they have the same target time of 30 minutes.

So my 1st query figures out how much time they spent in each room and also displays the target time (based on the room number which tells the room type & assigned target time).

Results of qry1:
TimeSheetID ElapsedTme TargetTime
1 46 30
1 68 30
1 27 30
1 45 30
1 30 30
2 65 30
2 50 30
2 30 30
2 55 30
2 9 30
2 11 30
9 32 30
9 30 30
9 35 30
9 90 40

My 2nd query is supposed to group by TimeSheetID, group by target time and average the actual time spent.

So if they spend 35 minutes on a Queen rm and 40 minutes on a King rm - since those have the same target time of 30 minutes - those two records should be averaged together.

But my 2nd qry is not doing that for all records ...
any idea why not?

2nd query SQL: SELECT qry1.TimeSheetID, qry1.TargetTime, Avg(qry1.ElapsedTme) AS AvgOfElapsedTme
FROM qry1
GROUP BY qry1.TimeSheetID, qry1.TargetTime;


2nd query results:
TimeSheetID TargetTime AvgOfElapsedTme
1 30 43.20
2 30 36.67
9 30 31.00
9 30 35.00
9 40 90.00


Note ID #9 - with the two target times of 30 min - it is not averaging the 31 and 35 minutes of Elapsed time.

Thanks

 

[Golom]

Check the data type for "TargetTime". If it is a floating point type (single or double) then there's a possibility that it is actually some value like 30.0000000000001 which would display as "30" but would not be regarded as identical to 30 for purposes of grouping.

 

[redwoodly]

it worked thanks.