Piggybacking on John Spencer's reply, I would define a set of Queries to
give you information about your schedule, but all based on the same
Table, which I call [Sched] here.
My Queries actually look more cumbersome than John's, but if you
structure your Table this way, it would be easier to add rooms or time
slots later. Plus, you could more easily calculate averages and other
statistics on room use.
[Sched] Table Datasheet View:
Date Room Slot
--------- ---- ----
11/1/2005 GH M
11/1/2005 QV M
12/2/2005 GH L
12/2/2005 GH M
12/3/2005 QV A
This Query lists any time slot that is used on a given date. (To use
the SQL, open a new Query in Design View, switch to SQL View, and paste
this SQL in place of what's there. But be sure the Table is in place
first.)
[Q_DatesSlots] SQL:
SELECT DISTINCT Sched.Date, Sched.Slot
FROM Sched
ORDER BY Sched.Date, Sched.Slot;
[Q_DatesSlots] Query Datasheet View:
Date Slot
--------- ----
11/1/2005 M
12/2/2005 L
12/2/2005 M
12/3/2005 A
This one lists how many time slots each room is used on a given date:
[Q_RoomsByDate] SQL:
TRANSFORM Count(Sched.Sched_ID) AS CountOfSched_ID
SELECT Sched.Date
FROM Sched
GROUP BY Sched.Date
ORDER BY Sched.Date, Sched.Room
PIVOT Sched.Room;
[Q_RoomsByDate] Query Datasheet View:
Date GH QV
--------- -- --
11/1/2005 1 1
12/2/2005 2
12/3/2005 1
This one lists how many time slots are scheduled in at least one room on
a given date. I'm not sure how this is useful to you, but that's what
your example looked like. Change this Query if it doesn't do what you want.
[Q_NumSlotsByDate] SQL:
SELECT DISTINCT Q_DatesSlots.Date,
Count(Q_DatesSlots.Slot) AS CountOfTime
FROM Q_DatesSlots
GROUP BY Q_DatesSlots.Date;
[Q_NumSlotsByDate] Query Datasheet View:
Date CountOfTime
--------- -----------
11/1/2005 1
12/2/2005 2
12/3/2005 1
This one lists which slots are scheduled (for any room) on a given date.
As before, this may not do what you want, so be careful.
[Q_SlotsByDate]
TRANSFORM -(Count([Sched].[Sched_ID])>0)
SELECT [Sched].[Date]
FROM Sched
GROUP BY [Sched].[Date]
PIVOT [Sched].[Slot] In ("M","L","A");
[Q_SlotsByDate] Query Datasheet View:
Date M L A
--------- --- --- ---
11/1/2005 1
12/2/2005 1 1
12/3/2005 1
This one links them all together, to look like your original Table:
[Q_FullSchedule] SQL:
SELECT Q_NumSlotsByDate.Date,
IIf(nz([Q_RoomsByDate]![GH],0)=0,"","Yes") AS GH,
IIf(nz([Q_RoomsByDate].[QV],0)=0,"","Yes") AS QV,
Q_NumSlotsByDate_1.CountOfTime AS [Time Slot Total],
IIf(nz([Q_SlotsByDate].[M],0)=0,"","Yes") AS Morning,
IIf(nz([Q_SlotsByDate].[L],0)=0,"","Yes") AS Lunch,
IIf(nz([Q_SlotsByDate].[A],0)=0,"","Yes") AS Afternoon
FROM Q_NumSlotsByDate AS Q_NumSlotsByDate_1
INNER JOIN ((Q_NumSlotsByDate INNER JOIN Q_RoomsByDate
ON Q_NumSlotsByDate.Date = Q_RoomsByDate.Date)
INNER JOIN Q_SlotsByDate
ON Q_RoomsByDate.Date = Q_SlotsByDate.Date)
ON Q_NumSlotsByDate_1.Date = Q_SlotsByDate.Date
ORDER BY Q_NumSlotsByDate.Date;
[Q_FullSchedule] Query Datasheet View:
Date GH QV Time Slot Total Morning Lunch Afternoon
--------- --- --- --------------- ------- ----- ---------
11/1/2005 Yes Yes 1 Yes
12/2/2005 Yes 2 Yes Yes
12/3/2005 Yes 1 Yes
I had some of the same questions that John had, but guessing at the
answers, I think these Queries may come close to what you wanted. And
with a restructured Table, other Queries will be easy to write.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
John said:
How do you record that QV was used for lunch and Afternoon and GH was used
for Morn on the same date? Or does that matter?
Personally, I would restructure your table to
UseDate: Date involved
Room: QV or GH
Slot: Morn, Lunch, or Afternoon
You would then have one record for each use of the room and your queries
would be simple.
With you present structure, I might try the following
Assumptions: Date is a datefield, Time slot total is a number field, all the
other fields are yes/no (boolean) fields.
SELECT SUM(QV * (Morn + Lunch + Afternoon)) as QVuse,
SUM(GH * (Morn + Lunch + Afternoon)) as GHuse
FROM [Your Table Name]
WHERE [Date] Between #1/1/2005# and #12/31/2005#
Post back if you need directions on building the query in the query grid.
Basically, make a totals query and use the two formulas as calculated fields
that get summed.
Field: QVuse: QV * (Morn + Lunch + Afternoon)
Total: Sum
Here is what my table looks like:
Date QV GH Time Slot Total Morn.
Lunch
Afternoon
Nov. 1 YES YES 1 YES
Dec. 2 YES 2 YES
YES
Dec. 3 Yes 1
YES
I want to know the total of time slots each room was used. Nov. 1 1 time
slot was used for both rooms
Dec. 2 2 time slots was used for only the GH room.
Dec. 3 1 time slot was used for QV room.
I'm unsure of how to get the total time slots each room is being used.
Also, my Time Slot is a total of Morning, Lunch and Afternoon. If someone
can help me that would be wonderful!!
Thank You,
Melissa
