Vacant Time Slots Query

[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.

The problem is how would create a query that would list any vacant time
slots not booked detailing the available time slot and WorkStationID. Many
thanks for taking the time to read this, any help appreciated.

Debbie D.

 

[Eric D via AccessMonster.com]

Ok... your tests take 45 min each.
You have 10 computers on which tests can be taken.
Tests can be taken on Mondays, bwtween 11:30 and 1:30 and on Thursdays,
between 5:00 and 9:00pm

The way you explain it... it sounds like you have 2-1 hour segments on Monday
and 3-1 hour segments on Thursday... is this correct or can a student book a
test at any time during these hours?

I'll go with the above assumption. This means that 20 students (max) can take
a test on Monday and 30 (max) on Thursday.

You have to create a table with the dates and times where tests are available.
This table would have to be updated periodically to keep current. Can
students take a test any week of the year? (not normal) I would design this
table to contain the Date, Time, WorkstationID (all three would form my
primary key to ensure uniqueness for every time slot) and StudentID (when
assigned).

Create a form, based on this table to be able to: 1. Search for Date, Time,
WorkstationID where StudentID is null. You could enter a singe requested date
or a date range here as your search criteria and provide results where
Date/Time/WorkstationID are listed based on StudentID being null. 2. Update
Table with StudentID when a student is assigned that time slot. Your next
search would be one less than the previous search.

Lots of ideas for bells and whistles here, but I hope this gets you started.

 

[Debbie D.]

Eric,

Thanks you very much put me right on track