Hi Karl:
I also have the feeling that it should be a cross tab. I have the following
tables:
Rooms:
Roomid
roomname
location
capacity
Users:
userid
FirstName
LastName
Position
Reservations:
Reservationid
roomid
userid
date
starttime
endtime
comments
I also created a table with all the available hours from 8:00 AM to 7:00 PM
in 30 minutes increments to try to use it as the column headers for the cross
tab but it does not work. Please let me know if you find that I am missing
something or if you have any ideas on how to go about it.
Thanks
I think something like this would work. There is a Calendar table
and a BookTimes table with half-hours. The Insert query fills the
RoomBookTimes table.
This is something similar:
http://www.psci.net/gramelsp/temp/PAtient Appointments.zip
CREATE TABLE BookTimes (
BookTime DATETIME NOT NULL,
PRIMARY KEY (BookTime)
);
CREATE TABLE Calendar (
CalendarDate DATETIME NOT NULL,
IsBusinessDay TINYINT DEFAULT 1 NOT NULL,
PRIMARY KEY (CalendarDate)
);
CREATE TABLE RoomBookTimes (
RoomID TEXT(5) NOT NULL,
CalendarDate DATETIME NOT NULL,
BookTime DATETIME NOT NULL,
PRIMARY KEY (RoomID, CalendarDate, BookTime)
);
CREATE TABLE RoomReservations (
RoomID TEXT(5) NOT NULL,
CalendarDate DATETIME NOT NULL,
BookTime DATETIME NOT NULL,
UserID LONG NOT NULL,
PRIMARY KEY (RoomID, CalendarDate, BookTime, UserID)
);
CREATE TABLE Rooms (
RoomID TEXT(5) NOT NULL,
PRIMARY KEY (RoomID)
);
CREATE TABLE Users (
UserID LONG NOT NULL,
UserName TEXT(50) NOT NULL,
PRIMARY KEY (UserID)
);
INSERT INTO RoomBookTimes ( RoomID, CalendarDate, BookTime )
SELECT Rooms.RoomID, Calendar.CalendarDate, BookTimes.BookTime
FROM Rooms, Calendar, BookTimes
WHERE Calendar.IsBusinessDay=1;
TRANSFORM First(Query1.UserName) AS FirstUserName
SELECT RoomBookTimes.RoomID, RoomBookTimes.CalendarDate
FROM RoomBookTimes LEFT JOIN (SELECT RoomReservations.RoomID,
RoomReservations.CalendarDate,
RoomReservations.BookTime,
Users.UserID,
Users.UserName
FROM RoomReservations
INNER JOIN Users
ON RoomReservations.UserID = Users.UserID) AS Query1 ON
(RoomBookTimes.RoomID = Query1.RoomID) AND (RoomBookTimes.CalendarDate = Query1.CalendarDate) AND
(RoomBookTimes.BookTime = Query1.BookTime)
GROUP BY RoomBookTimes.RoomID, RoomBookTimes.CalendarDate
PIVOT RoomBookTimes.BookTime;
