Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Question Unanswered: SQL Code - Checking for vacant rooms on hotel database

    I am a newbie to both SQL and Access and was wondering if you could help please?

    I am in the process of creating a hotel bookings database using Access 2010 but I cannot get my query working where I search for a vacant room.

    My database has 5 tables as follows (Field Names in brackets):

    BOOKINGS (BookRef, CustAcctNo, BookDate, ArrivDate, DurStay, EmpNo, RoomNo)
    CUSTOMERS (CustAcctNo, Title, Forename, Surname, Address1, Address2, Address3)
    EMPLOYEES (EmpNo, Title, Forename, Surname)
    ROOM TYPES (RoomType, Description, Rate/Price)
    ROOMS (RoomNo, RoomType)

    These tables all have a 'one-to-many' relationship i.e. one customer can have many bookings.

    So, my thinking is that the Fields of interest would be the ArrivDate Field (date of arrival) and DurStay (Duration of Stay) Field. In the Rooms table the Room Number is the Field I am calling out.

    So, the closest I have got so far is the following:

    PARAMETERS [Start Date] DateTime, [End Date] DateTime;
    SELECT R.*, [Start Date] AS Expr1, [End Date] AS Expr2, *
    FROM ROOMS AS R
    LEFT JOIN (SELECT B.RoomNo
    FROM Bookings AS B
    WHERE ([Start Date] between B.ArrivDate and (B.ArrivDate + [Please Enter]))
    OR ([End Date] between B.ArrivDate and (B.ArrivDate + B.DURSTAY))) AS BKD
    ON R.RoomNo = BKD.RoomNo
    WHERE (((BKD.RoomNo) Is Null));

    This just doesn't seem to be working for me at all. I have tried many times with different versions of the above code but seem to be getting nowhere. My thoughts were that I do a search where the field is null between the dates plus the duration of stay but maybe I am going about it the wrong way I am not sure. However, when I run my query it is returning rooms that are not vacant. An example is as follows:

    Room 12 has an arrival date of 10/09/2014 and duration of stay is 3 days. When I run the query Start Date 01/09/2014 and End Date 14/09/2014 it tells me that the room is vacant during these dates.

    Hopefully I have provided enough detail here but please let me know if you need to know more. I really appreciate you all having a look at this at least. Maybe a fresh outlook on it might spot where I am going wrong.

    Many thanks in advance for any help you can offer.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try using a sub query and the 'not exists' clause
    Microsoft Access tips: Subquery basics

    somethign like
    Code:
    SELECT rooms.no
    FROM Rooms
    WHERE NOT EXISTS
     (  SELECT roomno FROM bookings
        WHERE bookings.roomno = rooms.no
        AND bookings.arrival <= #2014/09/14#  
        AND dateadd("d",bookings.arrival,bookings.duration) <= #2014/09/14#
      );

    If I were you, I'd probably want to change the bookings table to record the departure date, rather than a duration. its not really that significant but it makes queries like this easier to read, easier on the eye and therefore less likely to be the source of an error. there is a theoretical perfomance increase (by not using an inbuilt VBA function not that anyone would notice, even if running on 90's era PC
    Last edited by healdem; 06-09-14 at 05:17.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •