Preventing Double Booking for a Hotel Room

[commint]

Hi, I'm currently building a database for a hotel as part of my Uni course.

I've managed to get a simple database going but I can't work out how to get the room availability to change when a new booking is made, so that the room becomes unavailable to re-book on the dates its booked for.

The room availability is on the rooms table and is currently a check box. The dates for the bookings are 2009 because that is the data that the lecturer has given us.

I'll attach a zip of my database to this post and any help will be much appreciated.

Thanks in advance!

View attachment lborohotel.Zip

 

[spikepl]

to check for overlaps in bookings: http://www.baldyweb.com/OverLap.htm

 

[jdraw]

For a sample database re room availability see this link

 

[sneuberg]

I don't think availability can be a attribute of room as it depends on time. If you just want to know what rooms are not booked at all then the follow unmatched query would answer give you those rooms:

SELECT Rooms.RoomNumber
FROM Rooms LEFT JOIN Booking ON Rooms.RoomNumber = Booking.Room
WHERE (((Booking.Room) Is Null));