Class Scheduling Query

[Sue]

I have a large training database that contains many records of future
training courses. For a course there could be only one class or multiple
classes. Where there are multiple classes, each class is held at the same
time for each class day. Some multiple classes may be on consecutive days,
once a week or once a month. The relative tables and fields are:
TblCourseClass
CourseClassID
CourseID
BeginTime
EndTime

TblCourseClassDate
CourseClassDateID
CourseClassID
CourseClassDate

I need a query to help me add a new course that is multiple classes to the
schedule. Given the number of classes, the time needed for the class
(EndTime - BeginTime), and whether the class is on consecutive days, once a
week or once a month, I need the query to find all the available days and
time slots the course can be scheduled.

Thanks!

Sue

 

[John Viescas]

Sue-

The general solution to find out if a "slot" is available involves checking
a selected date and time span against the records already saved. Let's say
you want to try to schedule a class on January 15, 2005 between 9a and 10a.
If the following query returns no rows, then the slot is available:

SELECT CourseClassID
FROM tblCourseClass INNER JOIN tblCourseClassDate
ON tblCourseClass.CourseClassID = tblCourseClassDate.CourseClassID
WHERE tblCourseClassDate.CourseClassDate = #01 JAN 2005#
AND tblCourseClass.BeginTime < #10:00:00 AM#
AND tblCourseClass.EndTime > #09:00:00 AM#

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)