Finding Available Appointment Time Slots

[MSingh]

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

[HansV]

Could you attach a small sample database (zipped)?

[burrina]

What determines when a Booking is available? Do you NOT have a room number or status? Confused by your post!

[MSingh]

Hi Hans,

Thank You.

Please see attached Bookings.zip

Many Thanks

Kind Regards,
Mohamed

[HansV]

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?

[HansV]

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.

[MSingh]

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

[MSingh]

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

[HansV]

Thanks. It's late here - going to sleep now. I'll look into it in the daytime.

[HansV]

(5) Why is LocationID in tblEvent and not in tblBookings?

(3) (Partial repeat) Can bookings occur on any day of the week, including weekends?

[MSingh]

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

[HansV]

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.

Bookings.zip

[MSingh]

Hi Hans,

Many Many Thanks - that works perfectly

Kindest Regards,
Mohamed

[MSingh]

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

[HansV]

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.

Bookings with Locations.zip

[burrina]

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.

[HansV]

Thanks, I hope Mohamed will find it useful.

[MSingh]

Hi Hans,

Thank You Kindly.
As always your solutions are excellent.

Kindest Regards,
Mohamed

[MSingh]

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