Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Totals query not grouping 1

Status
Not open for further replies.

redwoodly

Programmer
Apr 10, 2002
64
US
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top