Prvent double booking a room

[ian_ok]

Hi,

Long time since I've been on here, not much database work being done....anyhow here goes.

I'm designing a small d/b that would allow 4 rooms to be booked out, by date (not time) and I want to prevent double bookings.

I've woked out the sort of furmula, but am not sure if it is 100% correct and more important how to write the code, either as a query or a vb code?

Formula:

(A) Booked room start date < Booked room end date

AND

(B) NEW Booking start date < NEW Booking end date

AND

(C) NEW Booking end date < Booked room start date

OR

(D) Booked room end date < NEW Booking start date

I think a booking is possible if A & B & C OR D is true

But would this allow for rooms to be used for 1 night and then the guest to move to another if that was available.....

...not sure if I am making myself clear here....HOPE you can help.

Ian

 

[ColinEssex]

One way of doing this (not necessarily the best) is as follows

You have four rooms - A,B,C and D

You have a table with RoomID and BookedDate (plus other fields if you wish like BookedBy etc)

You then have a booking form where you select the room and enter a startDate and End Date. Then you append a row to the table for EACH day the room is booked. So room A booked for 1/4/03 to 3/4/03 will have 3 entries in the table.

On the booking form you can have an ActiveX calendar to select the dates and a ComboBox to select the Room. On the AfterUpdate of the date fields you could scan the Bookings table to check that its ok to be booked or not.

Thats thinking "on the fly" - I haven't done it myself but it'll give a start for you and maybe others will chip in with other ideas.

Col

 

[The_Doc_Man]

A search of this forum for topics from about 2-3 months ago should turn up several hits on keyword "BOOKINGS" that might shed considerable light on this subject.

This problem crops up every time an instructor hands it out for his or her students. It is quite familiar. Must be one of the really nasty term-paper projects at the back of someone's school book.

 

[geoffD]

Clashing Events/Appointments

You might try reading this site http://members.iinet.net.au/~allenbrowne/appevent.html
It has been around a few years but is still valid. I have used this technique in scheduling volunteers for marathons etc to insure that I haven't assigned them overlapping schedules.