Help with preventing double bookings with variable lengths

Question

I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks availability). I'm aware of the method where times are stored in a separate table (found this by searching on Google), but that doesn't work for me because members should be able to choose what time they'd like to start their booking.

The values that I want to be unique are: the date, the time and the venue room number. For example,

21/12/14, 16:45, Room 4

should not be compatible with

21/12/14, 16:45, Room 4

because all data is the same, but it should be compatible with

21/12/14, 16:45, Room 3

because the room number is different.

Also, I need to find a way where a person can't book the same room while it is being used for someone else. I've thought about using a start and end time for this, but I'm not sure how I could implement it.

If it helps, then I am using Microsoft Access to create this database.

I'd appreciate any help I can get to either problem, and thanks!

Answer 1

Lets say that we have two venues A and B with both a start date/time and a end date/time. We need to check whether their time spans overlap. They overlap if:

A.end > B.start AND A.start < B.end

Answer 2

The easiest way is to create a multiple field index for your bookings table. The table should have at least the three fields e.g. dtmDate, dtmTime, strRoom. Then in design view ribbon show the table indexes. Create a new index called Bookings. In the adjacent column select dtmDate. The next row down select dtmTime. The next row down select strRoom. Then for index properties select unique = Yes.

Effectively what you've done is prevented any occurrences of identical entries across the three fields.