Creating bookings while avoiding clashes
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.
- The database is in mdb format and can be
downloaded
here (28,234 bytes)
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:
- Display all possible times
- List the used bookings
- Exclude used bookings, leaving only available times for
selection
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.
Queries define the available time slots
Display all possible times
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;
List the used booking slots
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;
Exclude used bookings
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));
The form does the rest of the work
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:
- AppointmentDate has some code in the AfterUpdate event that
triggers a requery of Slot. AfterUpdate is used for this because
it’s an event that triggers once you finish updating (the values
in) a control. - Slot uses qryAvailable as its Row Source but also has a
filter to restrict the records to whatever date is selected in
AppointmentDate.
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.
