Query to show available class times

[scottb]

Hi everyone,
I am trying to build a query to show available classroom space to show the times a classroom does not have a class scheduled for a specified date and time range (selected from frmClassroomAvailability: txtStartDate, txtEndDate, cmbStartTime, cmbEndTime). I’m trying to build a query qryClassroomAvailability consisting of:
tblClasses: ClassID, CourseID, ClassroomID, FacilityID, ClassDate, ClassStartTime, ClassEndTime and
tblClassrooms: ClassroomID, FacilityID, ClassroomName, ClassroomSeats

The following shows the existing classes for a selected date/time range:

SELECT tblClasses.FacilityID, tblClasses.ClassDate, tblClasses.ClassStartTime, tblClasses.ClassEndTime, tblClassrooms.ClassroomID, tblClassrooms.ClassroomName, tblClassrooms.ClassroomSeats
FROM tblClasses INNER JOIN tblClassrooms ON tblClasses.ClassroomID = tblClassrooms.ClassroomID
WHERE (((tblClasses.FacilityID)=[Forms]![frmClassReports]![FacilityID]) AND ((tblClasses.ClassDate) Between [Forms]![frmClassroomAvailability]![txtStartDate] And [Forms]![frmClassroomAvailability]![txtEndDate]) AND ((tblClasses.ClassStartTime) Between [Forms]![frmClassroomAvailability]![cmbStartTime] And [Forms]![frmClassroomAvailability]![cmbEndTime]) AND ((tblClasses.ClassEndTime) Between [Forms]![frmClassroomAvailability]![cmbStartTime] And [Forms]![frmClassroomAvailability]![cmbEndTime]))
ORDER BY tblClasses.ClassDate, tblClasses.ClassStartTime;

I would like to show the times that a classroom is available for the selected date/time range. Is this possible showing the available times? Thank you for any help. -Scott

[HansV]

Could you attach a zipped sample database? It's very difficult to design such a query in a void...

[scottb]

Of course. Attached. Thank you.

[scottb]

Thanks

[HansV]

Thank you. I will look at it later today.

[HansV]

See if this does what you want:

Code: Select all

SELECT * 
FROM tblClassRooms
WHERE ClassRoomID Not In 
    (SELECT tblClassrooms.ClassroomID
    FROM tblClasses INNER JOIN tblClassrooms ON tblClasses.ClassroomID = tblClassrooms.ClassroomID
    WHERE tblClasses.FacilityID=[Forms]![frmClassReports]![FacilityID] AND
        tblClasses.ClassDate Between [Forms]![frmClassroomAvailability]![txtStartDate] And [Forms]![frmClassroomAvailability]![txtEndDate] AND 
        tblClasses.ClassStartTime<=[Forms]![frmClassroomAvailability]![cmbEndTime] AND 
        tblClasses.ClassEndTime>=[Forms]![frmClassroomAvailability]![cmbStartTime])

[scottb]

Thanks Hans. Is it possible in the instance a classroom has classes scheduled from 12pm-2pm (and the selected search start time is 7am and end time is 7pm) to show the classroom is available from 7am-12pm and from 2pm-7pm? Is there a way to have the query result show that? I appreciate your help.

[HansV]

I suspect that that's too difficult, but if I think of something I'll get back to you.

[scottb]

I thought it might be. As always grateful for your help. - Scott

[scottb]

Hans,
With the solution above, how would you include classdate from tblClasses)?
Thank you.

[HansV]

I'll have a look later today.

[scottb]

Hans,
I noticed one more thing. The query does not seem to recognize the facility ID on WHERE tblClasses.FacilityID=[Forms]![frmClassReports]![FacilityID]
Other facilities are appearing in the query result.
Thanks again.

[scottb]

Let me clarify. In my production db, FacilityID is access from frmClassReports in a textbox by =[Forms]![frmClassAssignments]![FacilityID]. Similar to the sample I uploaded with the difference being on the uploaded version I set the default value to the facility ID 14. The other queries/reports I am running recognize the facilityid with the approach I am using. Sorry for the confusion. Your sql works on the uploaded version but in my production db all facilities appear. Hope that makes sense. Thank you.

[HansV]

I'll add another facility to your sample database. That should make it easier to test.

[scottb]

In my production version, FacilityID on frmClassreports pulls FacilityID from another form (names frmClassAssignments) with =[Forms]![frmClassAssignments]![FacilityID].
Conceptually the user opens a facility, can open class assignments (frmClassassignments), from there can open reports (frmClassReports). I have the reports on frmClassReports referencing FacilityID with [Forms]![frmClassReports]![FacilityID] and they all find it. The only difference I can see is that there is one more layer of forms (frmClassroomAvailability) but I have other reports that use a form like that for date selections. Hope that makes sense. Thanks.

[HansV]

I assume that each classroom belongs to a specific facility.
If so, tblClassRooms should contain a field FacilityID, but tblClasses shouldn't, since tblClasses already contains a field ClassRoomID, and FacilityID depends on ClassRoomID.

tblTimes contains duplicate times. Since you display both 7 AM and 7 PM as 07:00, several PM times were entered as AM times. You should always display AM/PM or - preferably - use the 24 hour clock.

Does this work better?

SELECT *
FROM tblClassRooms
WHERE ClassRoomID Not In (SELECT tblClassrooms.ClassroomID
FROM tblClassrooms INNER JOIN tblClasses ON tblClassrooms.ClassroomID = tblClasses.ClassroomID
WHERE tblClassrooms.FacilityID=[Forms]![frmClassReports]![FacilityID] AND tblClasses.ClassDate Between [Forms]![frmClassroomAvailability]![txtStartDate] And [Forms]![frmClassroomAvailability]![txtEndDate] AND tblClasses.ClassStartTime<=[Forms]![frmClassroomAvailability]![cmbEndTime] AND tblClasses.ClassEndTime>=[Forms]![frmClassroomAvailability]![cmbStartTime]) AND tblClassRooms.FacilityID=[Forms]![frmClassReports]![FacilityID]

[scottb]

Yes this seems to work and I appreciate the design explanation. Makes sense now. Last request on this, could the classdate from tblClasses be added to the query result? Thank you again.

[HansV]

No, that makes no sense. The query returns classrooms available (not in use) during the time specified on frmClassroomAvailability. Since the classrooms are not in use, they cannot be linked to tblClasses.

[scottb]

Sorry my mistake. Not linked to tblclasses but was hoping the date the classroom was not in use (available) could be shown in the event a date range rather than specific date was provided as the criteria. Sorry for the confusion.

[HansV]

That'd be complicated again. Better reduce the range in frmClassroomAvailability.