Room Reservation

[Guest]

Hi all,
I'm working on a reservation database in access 2K. The problem I have is
that I want the user to specify a FROM DATE and an END DATE in a from to find
out what are the available rooms during that period specified. I know how to
show the rooms that are booked during the period, but not the available ones.
Any help with finding the logic behind this will be much appreciated.

Thanks a lot

 

[Guest]

You will need some way for your app to know how many total rooms you have
(i.e. room numbers set up in a table). If you already have this set up, then
you can pull the available rooms by running a query of the booked rooms
against the complete room list and look for a booked flag of null.

Your query could be something like this:

Select MasterList.RoomNumber
From MasterList Left Join BookedList On
MasterList.RoomNumber=BookedList.RoomNumber
Where BookedList.BookedFlag=Null;

"BookedList" and "MasterList" can be either tables or queries.

You could also run the "Find Unmatched Query Wizard" to get your list of
available rooms.

HTH
Jason

 

[Albert D. Kallal]

We know that the query to find a collision in the booking is very simple.

A booking collision occurs when:

RequestBookStartDate <= BookEndDate
and
RequestEndDate >= BookStartDate

If you run the above query, then that would give you all the possible
collision for a given date range. A fairy simply query.

So, to find all free rooms, you just eliminate all of the above from your
list, you could try something like:

select * from tblBooking where RoomID NOT in
(select RoomID from tblBooking where RequestBookStartDate <= BookEndDate
and
RequestEndDate >= BookStartDate)