Vacant Time Slots Schedule

[Debbie D.]

I have database, the purpose of which is for students to book a date and time
for a test. Tests can only be taken at the following days/times and last
45mins:
Mondays: 11:30 to 12:30 and 12:30 to 13:30
Thursdays: 17:00 to 18:00, 18:00 to 19:00 and 19:00 to 20:00

There are 10 computers which can be booked. A form takes the bookings and
stores them in a table named: TestAppointments the structure of which is:
ApptDate, AppTime WorkStationID StudentID (related through various tables)

Naturally the table only stores booked information regarding dates, times,
PC etc. This is great for generating a report when testing days coming along
and for person in charge of the room to know what PC and by whom have been
booked.

To create the opposite of the above I have a separate table that contains
all the possible yearly dates, days, time slot, and PCs that can be booked.
Would it be possible to relate both these tables so that I can generate a
report that displays vacant time slots in a second report. Many thanks for
taking the time to read this, any help appreciated.

Debbie D.

 

[David Lloyd]

Debbie:

Assuming your second table of all possible yearly appointments (I call it
AllAppointments, for this example) contains fields for ApptDate, AppTime,
and WorkstationID, you could write a query similar to the following to
display the vacant time slots.

SELECT ApptDate, AppTime, WorkstationID, ...
FROM AllAppointments LEFT JOIN TestAppointments ON TestAppointments.ApptDate
= AllAppointments.ApptDate AND TestAppointments.AppTime =
AllAppointments.AppTime AND TestAppointments.WorkstationID=
AllAppointments.WorkstationID
WHERE TestAppointments.ApptDate Is Null

This is an OUTER JOIN which will show all records from the AllAppointments
table where there is no match in the TestAppointments table.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have database, the purpose of which is for students to book a date and
time
for a test. Tests can only be taken at the following days/times and last
45mins:
Mondays: 11:30 to 12:30 and 12:30 to 13:30
Thursdays: 17:00 to 18:00, 18:00 to 19:00 and 19:00 to 20:00

There are 10 computers which can be booked. A form takes the bookings and
stores them in a table named: TestAppointments the structure of which is:
ApptDate, AppTime WorkStationID StudentID (related through various tables)

Naturally the table only stores booked information regarding dates, times,
PC etc. This is great for generating a report when testing days coming
along
and for person in charge of the room to know what PC and by whom have been
booked.

To create the opposite of the above I have a separate table that contains
all the possible yearly dates, days, time slot, and PCs that can be booked.
Would it be possible to relate both these tables so that I can generate a
report that displays vacant time slots in a second report. Many thanks for
taking the time to read this, any help appreciated.

Debbie D.

 

[PC Datasheet]

Debbie,

Send me an email to my email address below. I have a module that could be
added to your database that would work very well in your application. I will
send you a screen shot.

 

[Debbie D.]

Thanks for both of you. I'm going to send my database to you as requested.
Got a bit further but still no final answer. I'm getting close but I think a
lot of it is down to the bad design of my database. Posting another question
soon on this matter. Really appreciate all your efforts and that of the
group.

Debbie D.