Results 1 to 7 of 7
  1. #1
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715

    Room Reservation -- Room Availability for specific days

    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).
    Attached Files Attached Files
    Last edited by orange; 01-27-2015 at 08:43 PM. Reason: emphasized the constraint that no Checkin on same day as CheckOut

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    Interesting problem. For starters, I don't know if you'd find this simpler:

    http://www.baldyweb.com/OverLap.htm

    But if this is for hotels, isn't there a fly in the ointment? Two separate reservations can start and end on the same day. I check out the morning of the 27th, you can check into the same room the afternoon of the 27th. Hotels must take time into account, or allow a start the same day as an end. Of course, hotels probably don't actually book specific rooms.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    It's a demo only Paul. The issue was Hotel Availability and the OP didn't deal with CheckIn time or CheckOut time which, I agree, is not too realistic. But it was really an issue to determine what rooms were available on the dates listed. I saw it as a the Cartesian product minus what wasn't available.
    If somebody wanted to get specific with CheckIn CheckOut times and availability of room after CheckOut on same day, and incorporate a number of other features (smoking/not smoking, King/Queen/Double/2 Doubles/ 2 Doubles and a cot, not near an elevator.....) then do it and expand on this and make it a reservation system.

    I intended the post as a demo on room availability. This was not intended as a Hotel/Motel booking/reservation system.
    Last edited by orange; 01-28-2015 at 12:52 PM.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Further to the earlier posts in this thread, and Paul's sample:

    I realize that it is unrealistic to not allow a booking on the same day after the current occupant has checked out.
    So, for illustration, I have a new table that includes Checkout time 11:00:00 AM, and I modified one of the Bookings #4 to have Checkout on 28 Jan 2015.

    TblRoom remains as shown in post #1

    The new table
    BookingId RoomId CheckIn CheckOut
    1 1 26/01/2015 27/01/2015 11:00:00 AM
    2 1 29/01/2015 01/02/2015 11:00:00 AM
    3 1 05/02/2015 06/02/2015 11:00:00 AM
    4
    2 24/01/2015 28/01/2015 11:00:00 AM
    5 3 15/01/2015 06/02/2015 11:00:00 AM

    The booking in blue shows the revised Checkout, and it is presumed the person checked out before 11 AM.

    I have also modified the SQL to show another set up to find available rooms for a room request for 28 Jan 2015 through 30 Jan 2015.


    The new sql
    Code:
    SELECT tblRoom.RoomID, tblBooking_V2.CheckOut, tblBooking_V2.CheckIn
    FROM tblBooking_V2 RIGHT JOIN tblRoom ON tblBooking_V2.RoomId = tblRoom.RoomID
    WHERE 
    tblRoom.roomid NOT IN
     (Select roomid from tblBooking where
      CHECKIN <=#30/1/2015 11:00:00am# AND
      CHECKOUT >=#28/1/2015 11:00:00am#)
    Note: This sql select
    (Select roomid from tblBooking where
    CHECKIN <=#30/1/2015 11:00:00am# AND
    CHECKOUT >=#28/1/2015 11:00:00am#)


    finds Rooms with bookings that start or end within the required reservation period, or with bookings that
    span the required reservation period. It is the NOT IN that changes the query to find Rooms that are NOT is this set of records.

    This is the result of the query and shows Rooms available for the required reservation.

    RoomID CheckOut checkin
    2 28/01/2015 11:00:00 AM 24/01/2015
    4


    I am attaching an updated version of the database which reflects the changes mentioned here.
    Attached Files Attached Files

  5. #5
    jasgra2015 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    5
    Is there a way to modify this system to have a easier way to view checked out rooms and a way to automate the check in process through using a form or something along those lines.... I am trying to use this setup as part of a bigger hotel PMS system but im a little lost on exactly how this setup works

  6. #6
    jaknap is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    2
    In a traditional database, managing booking and scheduling becomes really messy. Some newer databases actually have Calendly like scheduling built in as a field type.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    jaknap,
    See this thread that may offer some insight on use of a calendar.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query for Room Availability
    By Troop in forum Access
    Replies: 1
    Last Post: 05-19-2013, 10:38 AM
  2. Architects room data sheets
    By ThatSkepTicGuy in forum Forms
    Replies: 18
    Last Post: 09-19-2012, 09:12 AM
  3. Architects Room Data Sheets
    By ThatSkepTicGuy in forum Database Design
    Replies: 3
    Last Post: 09-10-2012, 10:37 AM
  4. How to get a room linked to a resident staying in it
    By Chelcone in forum Database Design
    Replies: 13
    Last Post: 05-18-2012, 09:28 AM
  5. Need help with room update query.
    By DevilMaster18 in forum Queries
    Replies: 3
    Last Post: 04-30-2010, 02:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums