How to Prevent Double-Bookings
A common type of A level project is a booking system created in Access. One of the most important functions such a system will perform is to prevent double-bookings or clashes. This can be more or less tricky, depending on how the appointments are made. Appointments or bookings fall into two basic categories - either discrete slots (e.g. a doctor's surgery, where a patient is given a 10-minute slot), or bookings with a start and end time/date (e.g. a booking in a holiday cottage, with arrival and departure dates).
If you're booking hour lessons, or discrete appointments of some sort, then you can use referential integrity to stop double-bookings. If you're booking something like holiday cottages, on the other hand, where there are arrival or departure dates and the bookings can overlap, then it's a little more tricky. Here I shall propose the methods I would use to prevent double-bookings in each case.
Discrete Appointments
On the right you can see the tables and relationships from appointments.mdb (download as an .mdb or .accdb file). It's the classic three-table booking system, with patients, doctors, and a linking appointments table. I've also added a fourth table (tbl_available) that contains a list of all the available appointment slots for each doctor. To make things easier, in this example the surgery has the same appointment times each day, although you could also add the day of the week if you wanted to vary their times.
Referential integrity will prevent the receptionist from making a booking for a doctor in a time slot that doesn't exist in tbl_available, but what happens if the appointment is already taken on that particular day?
One of the tidiest ways to prevent double-bookings is to use a combo-box to present the user with only the available appointments for a given day (see the form on the left). This can be populated from a query, and requeried whenever the date and/or doctor are changed.
Although the idea of the query - to find available slots on a given day - sounds quite simple, it's not as easy as it first appears. This is because you're looking not just for bookings that don't exist, but bookings that don't exist on a particular day. My solution, therefore, uses two queries - one that finds the appointments that have been made for a particular doctor on a given day (straightforward enough), and then a second one that effectively subtracts these from the complete list of available slots (i.e. those in tbl_available), to give the available appointments for the chosen day.
The illustration on the right shows this second query. Note that it contains a table and a query - you will have to create the relationship yourself (you do this by dragging the time field from the table to the query, as you would if you were joining two tables). You also need to change the Join Properties (right click on the line joining the table and the query), to select the middle option - i.e. to show all the records from tbl_available...
Once you've done that, the query itself is quite simple - you just want all the times for your doctor from tbl_available where there isn't a corresponding time in qry_appointments_on_day - i.e. where the time is NULL.
If you now use the form to make a booking for a particular doctor on a given day, you will notice that the time disappears from the list once you've clicked Save, so that that slot can't be used again.
Room Bookings
I've also created another booking example that uses discrete slots and referential integrity to prevent double-bookings - click the links to download the Room Bookings database (in either .mdb or .accdb format). Please note that the bookings form may give an error if your username is not entered in the tbl_staff table before you use the system.
The is effectively the same as the doctors example - when you book a room or facility it is checked against both the timetable and previous bookings to ensure that it is not double-booked. To get the database to work correctly for you, here's what you need to put in it:
tbl_available:
This is a list of what is available and when - we have a two-week timetable, so I've included a week in there, and the day is stored as a number (e.g. Monday = 1, Tuesday = 2, etc.) so that they can be sorted easily (rather inconveniently, the days of the week aren't in alphabetical order!).
tbl_bookings:
This is where the bookings are stored - you don't need to put anything in here.
tbl_days:
The days of the week, so that the numbers can be converted back into names.
tbl_facility:
A list of the things you want to be able to book, together with a description and an indication of whether it's a room.
tbl_lesson_times:
A list of the periods and what time they start (the ASP version shows the current period by default). If you don't have a five period day, just adjust the number of records in here - you could add extra periods for after-school, etc.
tbl_multimedia:
This is used to store what software licences are available in each room.
tbl_staff:
A list of staff so that you can book the room to a particular person, and the department so that you can track which department doesn't use ICT! Make the staff code the same as the network username, and the bookings form will default the current user.
tbl_weeks:
This needs to contain a list of all the Mondays in the year, and what week of the timetable it is. If you have a one-week timetable, just make them all week 1.
There is an HTML/ASP user interface for this database in the Scripting section.
Variable-Length Bookings
Preventing clashes with variable-length bookings is a little more involved. If you've trawled the newsgroups and Access FAQs, then you may have come across the cartesian product method for picking up double-bookings. The problem with this approach is that you do the checking after the record has been saved, by which time it is too late.
In clash_test.mdb (click here for the .accdb version) I've proposed a method for preventing the user from saving clashing bookings in the first place. It isn't overly elegant, and you may need to tweak the events on which the query is re-run to suit your needs, but hopefully it'll give you some idea of how to approach a solution.
You could, of course, use a similar method to the one employed in the doctors' surgery solution - it would just require a more complex query to provide a list of available rooms on a given date. The method I describe here allows you to select any room, but then validates it to check that it is available for the entire period selected.
What happens is that the form used to enter the date has two hidden fields containing the dates of the latest prior booking, and the first subsequent booking, in the same room. When the Save Record button is clicked, a macro compares the dates and stops the save if the dates overlap.
How it Works in Detail
The database itself is just a simple three-table affair, with tables for rooms, customers, and bookings. Generally, things like customer codes are frowned upon, but I've just used them here for simplicity. I've added fields for the number of guests in the party, but I haven't actually done anything with them - the point of the database is only to demonstrate the double-booking validation.
On top of the bookings table, I've created a form (shown to the left) that contains the fields in the table. I've used combo boxes for the customer and room number to make it easier to select rooms and customers that exist and maintain referential integrity.
Underneath the Save Record and Cancel buttons are two invisible combo-boxes that are populated from the two queries, qry_previous_booking and qry_next_booking. The visible property of both combo-boxes is set to False so that they are hidden.
The query qry_previous_booking finds the latest prior booking in the same room, based on the dates and room you enter. Because you might change the dates or the room number, you need to put a .requery command on the LostFocus or Change events to re-run the query. All that does is ensures that the next and previous bookings always relate to the rooms and dates you have entered.
The next step in the validation process is to check that the appointments don't overlap. This needs to be done before the record is saved, so that you can warn the user and request alternative dates or a different room before it's too late. The easiest way to do this is to add the Save button using the normal wizard, and then add some VBA code afterwards.
The code just uses a simple if... then... else... loop to display a message if the appointment clashes with another, or save the record if it doesn't. To determine whether the start of the new booking comes before the end of the previous one, or whether the end of the new booking comes after the start of the next one, I've used the DateDiff() function. This will give either a positive or a negative answer, depending on which date comes first. You might find it simpler to try a simple comparison, but I've found that > and < don't always work reliably with dates.
The code for the Save button is shown below. Note that you can put most of the if statements on one line, and omit the End Ifs - I've just done it that way so that the code isn't too wide to fit on the screen here.
One last thing to bear in mind is that the record will still be saved if you click on the button at the top of the window to close the form. You can get around this either by disabling the Close button (in Form properties), or by adding similar code to the form's Close event to check for clashes there too.
Multiple Slots
There is a half-way house between the discrete slot and completely variable approaches, which is to book multiple slots - doctors, dentists and hairdressers often take this approach, e.g. you book a double appointment if you need a filling.
Below is a link to an incomplete solution for a hairdressers' shops - it is part of an A level project that was e-mailed to me as the student was having trouble getting the variable-length approach to work. See if you can see how it works - it's more like the doctors' surgery than the variable-length bookings.
The StartTime combo shows you all of the unbooked start times, and once you've selected one, the EndTime combo shows all of the possible end times (you might like to make these a minute earlier to avoid confusion) up until the next appointment. The complication is that, if there are no later appointments, you need to show the end times up until the end of the day.