Room Reservation

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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)
 
Back
Top