Query to show available class times
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Query to show available class times
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
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
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
Could you attach a zipped sample database? It's very difficult to design such a query in a void...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
Of course. Attached. Thank you.
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
Thanks
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
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])
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
I suspect that that's too difficult, but if I think of something I'll get back to you.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
I thought it might be. As always grateful for your help. - Scott
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
Hans,
With the solution above, how would you include classdate from tblClasses)?
Thank you.
With the solution above, how would you include classdate from tblClasses)?
Thank you.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
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.
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
I'll add another facility to your sample database. That should make it easier to test.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
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]
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]
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Query to show available class times
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.
-
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to show available class times
That'd be complicated again. Better reduce the range in frmClassroomAvailability.
Best wishes,
Hans
Hans