This sample was created in response to a question on MrExcel.com. It demonstrates a way to assign timeslots in a booking form, and avoid clashes.
If you're ever called upon to make a booking system, one component of that is how to create bookings while preventing clashes or double-bookings. This could apply to a doctor's surgery or a tennis centre, to mention just two. The approach shown here uses three queries to do most of the work.
In turn, these queries do the following:
But first, the table structure. The tables in the sample database are shown below. Only fields relevant to the timeslots have been added; you will need to add your own fields to suit the rest of your database solution.
The first step is to display all of the possible time slots. The working days are in tblDates, the appointment slots are defined in tblSlots, and qryApptSlots uses a cartesian join to show all possible combinations. In addition, because dates and times are stored as numbers you can add them together, so the query has a calculated field called ApptKey to generate a unique value for each combination. The SQL for the query is shown below.
SELECT tblDates.AppointmentDate, tblSlots.Slot, tblSlots.StartTime, tblSlots.EndTime, [AppointmentDate]+[StartTime] AS ApptKey FROM tblDates, tblSlots;
To list the used booking slots, qryBooked uses AppointmentDate and StartTime from tblAppointments, and then calculates the appointment key in the same way as the first query. Here is the SQL:
SELECT tblAppointments.AppointmentDate, tblAppointments.StartTime, [AppointmentDate]+[StartTime] AS ApptKey FROM tblAppointments;
qryAvailable uses the first two queries to find which slots remain. By creating an outer join between qryApptSlots and qryBooked, and filtering for ApptKey values that don't exist in the bookings table, you end up with a list of the available bookings. This is the SQL:
SELECT qryApptSlots.AppointmentDate, qryApptSlots.Slot, qryApptSlots.StartTime FROM qryApptSlots LEFT JOIN qryBooked ON qryApptSlots.ApptKey = qryBooked.ApptKey WHERE (((qryBooked.ApptKey) Is Null));
By this stage we have a query that displays all available bookings. It still isn't filtered down to any single day, but that work is done by the two combo boxes in frmAppointments.
AppointmentDate and Slot are the two fields of interest on the form; everything else is hidden. When you pick a date in AppointmentDate, the Slot drop-down is filtered to show just the available slots. If you haven't used paired (or cascading) combo boxes before, this is how it works:
You can check out the detail by looking at the form in the sample download, but as you can see it's a loop. The upstream combo box forces a requery of the downstream combo box, which in turn is filtered on whatever selection you have made in the upstream combo.
In addition, the AfterUpdate event of the Slot combo box is used to populate StartTime on the form. This could also be done by using a query to join tblAppointments and tblSlots, and simply placing the StartTime field on the form, but in this case I chose to break the rule about not storing the same data in multiple locations, because it simplifies the queries and the 'pushed' data is not available for editing on the form.
This is by no means the only way to avoid clashes in a booking system, but it's relatively simple to set up. I hope you find it helpful.