Unmatced Record per day

  • Thread starter Thread starter Michael Noblet
  • Start date Start date
M

Michael Noblet

I have a query that gives me the Utilization of a room.
it has a start time ans an end time for each room and a
use date.

I am trying to create a query that will show me the rooms
that are not used for a particular date. Which I will
then ad to my report of utilization so the room shows up
as not being used.

Any ideas on how to make this happen?

Mike
 
Michel,

You need to start out with a table that contains a list of all the rooms
(tbl_Rooms). I'll assume your other table is tbl_RoomSchedule
Then, to get a list of all the rooms, and when they were utilized on a
particular day, you need to create a query to identify which rooms were
used.

SELECT RoomNum
, SUM (DateDiff("n", [StartTime], [EndTime])) as MinutesUsed
FROM tbl_RoomSchedule
WHERE UseDate = [What Date]

You can then encorporate this in a more complex query to identify all the
rooms and the number of minutes each was used on that day.

SELECT R.RoomNum, NZ(RS.MinutesUsed, 0) as Usage
FROM tbl_Rooms R
LEFT JOIN
(SELECT RoomNum
, SUM (DateDiff("n", [StartTime], [EndTime])) as MinutesUsed
FROM tbl_RoomSchedule
WHERE UseDate = [What Date]) as RS
ON R.RoomNum = RS.RoomNum

This will give you a list of all the rooms and the number of minutes they
were used, including those rooms that were not used at all.

HTH
Dale
 
Back
Top