The purpose of this post is to show a technique to find out which Rooms are available for rent for a specific period.
(This is simply one way to find what rooms are available during a specific period. The constraints
are not realistic to a real Hotel/Motel, but for the demo there is no CheckIn on the same day as CheckOut.)
The post is based partly on https://www.accessforums.net/queries...ity-49813.html. However, I have added
another room and booking.
I have modified the query to use Parameters (ResStart and ResEnd), but this demo is to find out which Rooms are available for rent for 28-Jan-2015 to 30-Jan-2015, so you should use those Dates for the demo.
There are 2 tables:
tblRoom
RoomId autonumber PK
RoomType text
tblBooking
BookingId autonumber PK
RoomID number
CheckIn Date/Time
CheckOut Date/Time
The rentable rooms in this imaginary Hotel/Motel:
RoomID |
RoomType |
1 |
Double |
2 |
Double |
3 |
Single Non Smoking |
4 |
Single |
The Bookings when you arrive looking for a room to rent from Jan 28 2015 to Jan 30 2015:
BookingId |
RoomId |
CheckIn |
CheckOut |
1 |
1 |
26/01/2015 |
27/01/2015 |
2 |
1 |
29/01/2015 |
01/02/2015 |
3 |
1 |
05/02/2015 |
06/02/2015 |
4 |
2 |
24/01/2015 |
26/01/2015 |
5 |
3 |
15/01/2015 |
06/02/2015 |
There are many ways to find availability, but this one may be a little different.
The task is to find out which rooms are available - not already rented - between Jan 28 and Jan 30 2015.
This can be translated into - What are all the rooms and which of those are NOT available. So let's see
which rooms are not available.
* which rooms are only available part of the time we need, that is
reservations that end during the time we need, and
reservations that start during the time we need
- which rooms do not have a CheckIn between our ResStart and ResEnd
- which rooms do not have a CheckOut between our ResStart and ResEnd
and
* which rooms are not available during all of the time we need, that is a reservation
that started before our ResStart and has a checkout date later than our ResEnd
(this is a reservation that spans the time period we need)
-which rooms have a CheckIn < ResStart and CheckOut > ResEnd
The items in green represent rooms that are Not available.
So this approach identifies all the Rooms and all the Bookings (a Cartesian Product) and then
removes those that are Not available. Or saying that mathematically,
RoomsAvailable = AllRooms - UnAvailableRooms
So in SQL syntax of the query, RoomsAvailable is as follows:
Code:
PARAMETERS ResStart DateTime, ResEnd DateTime;
SELECT tblRoom.roomID
FROM tblRoom
WHERE (tblRoom.roomID) NOT IN
( SELECT DISTINCT Roomid FROM
(
SELECT tblRoom.RoomID, tblBooking.CheckIn, tblBooking.CheckOut
FROM tblBooking, tblRoom
WHERE
tblBooking.Roomid = tblRoom.roomID
And
(
((([tblBooking].[CheckIn]) Between ResStart AND ResEnd))
Or
((([tblBooking].[CheckOut]) Between ResStart And ResEnd))
Or
((([tblBooking].[CheckIn])< ResStart) And (([tblBooking].[CheckOut])>ResEnd))
)
)
);
The Rooms meeting available to our need are 2 and 4.
Note that Room 4 does not appear in tblBooking. (Perhaps it has not yet been rented).