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

TblCourseClassD ate
CourseClassDate ID
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]

Re: Class Scheduling Query

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 tblCourseClassD ate
ON tblCourseClass. CourseClassID = tblCourseClassD ate.CourseClass ID
WHERE tblCourseClassD ate.CourseClass Date = #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)
"Sue" <smullin@please .nospam> wrote in message
news:G0Ird.1469 $Va5.1286@newsr ead3.news.atl.e arthlink.net...[color=blue]
>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
>
> TblCourseClassD ate
> CourseClassDate ID
> 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
>
>[/color]