Unmatced Record per day

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
 
D

Dale Fye

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
 
Top