Finding Available Appointment Time Slots
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Finding Available Appointment Time Slots
Hi,
I'm trying to find available time slots for bookings.
tblBookings has the ffg. fields:
BookingID AutoNumber
BookingStartDateTime Date/Time (Start Date & Time of the booking)
BookingEndDateTime Date/Time (End Date & Time of the booking)
CustomerID LongInteger FK: tlblCustomers
BookingNotes Text
qryCartesianAllBookingDateTimes has tblDate (tblDate.TheDate has dates for the current month) & tblTimeSlots (tblTimeSlots.TheTime has the times when bookings can be made).
Bookings could be of varying length.
How can i find TimeSlots that have not been booked ?
Your assistance is greatly appreciated.
Kind Regards,
Mohamed
I'm trying to find available time slots for bookings.
tblBookings has the ffg. fields:
BookingID AutoNumber
BookingStartDateTime Date/Time (Start Date & Time of the booking)
BookingEndDateTime Date/Time (End Date & Time of the booking)
CustomerID LongInteger FK: tlblCustomers
BookingNotes Text
qryCartesianAllBookingDateTimes has tblDate (tblDate.TheDate has dates for the current month) & tblTimeSlots (tblTimeSlots.TheTime has the times when bookings can be made).
Bookings could be of varying length.
How can i find TimeSlots that have not been booked ?
Your assistance is greatly appreciated.
Kind Regards,
Mohamed
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
Could you attach a small sample database (zipped)?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Finding Available Appointment Time Slots
What determines when a Booking is available? Do you NOT have a room number or status? Confused by your post!
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi Hans,
Thank You.
Please see attached Bookings.zip
Many Thanks
Kind Regards,
Mohamed
Thank You.
Please see attached Bookings.zip
Many Thanks
Kind Regards,
Mohamed
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
Some questions:
(1) How do tblBookings and tblEvent relate? They appear to contain duplicate information.
I don't see a table tblTimeSlots.
(2) Do tblDate and tblBookingTimes take the place of tblTimeSlots?
(3) If so, does that mean that events can take place 7 days a week, 24 hours a day?
(4) Why does tblBookingTimes start at 01:00 instead of at midnight?
(1) How do tblBookings and tblEvent relate? They appear to contain duplicate information.
I don't see a table tblTimeSlots.
(2) Do tblDate and tblBookingTimes take the place of tblTimeSlots?
(3) If so, does that mean that events can take place 7 days a week, 24 hours a day?
(4) Why does tblBookingTimes start at 01:00 instead of at midnight?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
Oops, I see that you have a query qryCartesianAllBookingDateTimes that limits the times from 08:00 to 16:00. But still 7 days a week.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
HI Hans,
This is actually a quick extract from a larger database.
Regarding your questions:
1. tblEvent is unrelated to tblBookings ; it's my quick and dirty check for clashes, therefore the delete and append queries.
2. Yes, tblBookingTimes replaces tblTimeSlots.
3 & 4. tblBookingTimes is used elsewhere in the database, therefore the query limiting the booking times to between 08:00 and 16:00. Please excuse any confusion this may have caused, the query should be used instead.
A few additional notes:
1. Customers do not always turn up for bookings, therefore double bookings are permitted.
2. It could happen that a customer sends a proxy to the meeting while the customer is elsewhere in the building.
3. Certain customers are long - standing customers & are never double - booked, therefore the need to establish the available time slots.
Kind Regards,
Mohamed
This is actually a quick extract from a larger database.
Regarding your questions:
1. tblEvent is unrelated to tblBookings ; it's my quick and dirty check for clashes, therefore the delete and append queries.
2. Yes, tblBookingTimes replaces tblTimeSlots.
3 & 4. tblBookingTimes is used elsewhere in the database, therefore the query limiting the booking times to between 08:00 and 16:00. Please excuse any confusion this may have caused, the query should be used instead.
A few additional notes:
1. Customers do not always turn up for bookings, therefore double bookings are permitted.
2. It could happen that a customer sends a proxy to the meeting while the customer is elsewhere in the building.
3. Certain customers are long - standing customers & are never double - booked, therefore the need to establish the available time slots.
Kind Regards,
Mohamed
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
HI Hans,
This is actually a quick extract from a larger database.
Regarding your questions:
1. tblEvent is unrelated to tblBookings ; it's my quick and dirty check for clashes, therefore the delete and append queries.
2. Yes, tblBookingTimes replaces tblTimeSlots.
3 & 4. tblBookingTimes is used elsewhere in the database, therefore the query limiting the booking times to between 08:00 and 16:00. Please excuse any confusion this may have caused, the query should be used instead.
A few additional notes:
1. Customers do not always turn up for bookings, therefore double bookings are permitted.
2. It could happen that a customer sends a proxy to the meeting while the customer is elsewhere in the building.
3. Certain customers are long - standing customers & are never double - booked, therefore the need to establish the available time slots.
Kind Regards,
Mohamed
This is actually a quick extract from a larger database.
Regarding your questions:
1. tblEvent is unrelated to tblBookings ; it's my quick and dirty check for clashes, therefore the delete and append queries.
2. Yes, tblBookingTimes replaces tblTimeSlots.
3 & 4. tblBookingTimes is used elsewhere in the database, therefore the query limiting the booking times to between 08:00 and 16:00. Please excuse any confusion this may have caused, the query should be used instead.
A few additional notes:
1. Customers do not always turn up for bookings, therefore double bookings are permitted.
2. It could happen that a customer sends a proxy to the meeting while the customer is elsewhere in the building.
3. Certain customers are long - standing customers & are never double - booked, therefore the need to establish the available time slots.
Kind Regards,
Mohamed
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
Thanks. It's late here - going to sleep now. I'll look into it in the daytime.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
(5) Why is LocationID in tblEvent and not in tblBookings?
(3) (Partial repeat) Can bookings occur on any day of the week, including weekends?
(3) (Partial repeat) Can bookings occur on any day of the week, including weekends?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi Hans,
For simplicity, let's ignore tblEvent.
tblEvent is used as a temp table in the database from which this is an extract merely to be aware of the clashing bookings.
(Maybe another way to view this database is:
A (single practitioner) medical practice which has 1 consulting room that is manned 24 hours a day by medical professionals on a shift basis. The patient doesn't have a choice of practitioner & will be attended to by whichever practitioner is available in the only consulting room that is in the facility.
OR
Perhaps a 24 hour Vet Practice which is the only practice in the area which is opened 24/7/365 days a year.
Yes, bookings could be made any day of the week including weekends.
Kind Regards,
Mohamed
For simplicity, let's ignore tblEvent.
tblEvent is used as a temp table in the database from which this is an extract merely to be aware of the clashing bookings.
(Maybe another way to view this database is:
A (single practitioner) medical practice which has 1 consulting room that is manned 24 hours a day by medical professionals on a shift basis. The patient doesn't have a choice of practitioner & will be attended to by whichever practitioner is available in the only consulting room that is in the facility.
OR
Perhaps a 24 hour Vet Practice which is the only practice in the area which is opened 24/7/365 days a year.
Yes, bookings could be made any day of the week including weekends.
Kind Regards,
Mohamed
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
See if you can use the queries in the attached version. qryOccupiedSlots list all time slots that are already booked, and qryFreeSlots lists the time slots that are available.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi Hans,
Many Many Thanks - that works perfectly
Kindest Regards,
Mohamed
Many Many Thanks - that works perfectly
Kindest Regards,
Mohamed
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi Hans,
The questions you asked prompted me to think of the situation where the Location is vital to a booking eg. medical bookings with multiple examination rooms.
I've attached Bookings with Locations.zip which is your Bookings.zip with tluLocations.
Could you suggest how to find the available time slots for each room ?
(I tried including the LocationID in your queries with the same join-type, but the results are incorrect).
Kind Regards,
Mohamed
The questions you asked prompted me to think of the situation where the Location is vital to a booking eg. medical bookings with multiple examination rooms.
I've attached Bookings with Locations.zip which is your Bookings.zip with tluLocations.
Could you suggest how to find the available time slots for each room ?
(I tried including the LocationID in your queries with the same join-type, but the results are incorrect).
Kind Regards,
Mohamed
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
tblBookings contains conflicting bookings. For example, BookingID 24 overlaps with BookingID 32 on 19 August (both location 2), and BookingID 26 overlaps with BookingIDs 21, 23, 31 and 34.
So the number of records returned by qryOccupiedSlots is larger than the number of actually occupied slots. you can get around this by setting the Unique Values property of the query to Yes.
I have attached a version that consistently uses qryCartesianAllBookingDateTimesLocations.
So the number of records returned by qryOccupiedSlots is larger than the number of actually occupied slots. you can get around this by setting the Unique Values property of the query to Yes.
I have attached a version that consistently uses qryCartesianAllBookingDateTimesLocations.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Finding Available Appointment Time Slots
Pardon me for jumping in here, but I have been following this thread and found it interesting. Just for fun I put together a quick DEMO. It MAY help to visualize where he is going, maybe not? Anyway here it is.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding Available Appointment Time Slots
Thanks, I hope Mohamed will find it useful.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi Hans,
Thank You Kindly.
As always your solutions are excellent.
Kindest Regards,
Mohamed
Thank You Kindly.
As always your solutions are excellent.
Kindest Regards,
Mohamed
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Finding Available Appointment Time Slots
Hi burrina,
Please excuse the address, i did not see your name.
Many Thanks for your suggestions & for taking the time to design & post a working solution with forms.
Thank You for Sharing, Your Efforts are Appreciated.
Kindest Regards,
Mohamed
Please excuse the address, i did not see your name.
Many Thanks for your suggestions & for taking the time to design & post a working solution with forms.
Thank You for Sharing, Your Efforts are Appreciated.
Kindest Regards,
Mohamed