DataWright Information Services

Consulting and Resources for Excel and Access

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.

the table structure for the booking sample database

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.