Hotel Database - SQL Code for Vacant room check

[shauneyd]

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.

[June7]

Can you provide db for analysis? Follow instructions at bottom of my post. Include some dummy records for testing.

[shauneyd]

Can you provide db for analysis? Follow instructions at bottom of my post. Include some dummy records for testing.

Hello June7,

Thank you for your reply. I am having some difficulty compressing my db to 2mb or lower. I am currently at 3mb. Will upload when I get it down to 2mb. Thank you.

[June7]

Be sure to run Compact & Repair first.

Alternative is upload to fileshare site such as Box.com and post link to file.

[shauneyd]

Be sure to run Compact & Repair first.

Alternative is upload to fileshare site such as Box.com and post link to file.

Thank you June. Here is the link to it. I tried compressing and compacting it but to no avail. Here is the link to it: https://www.dropbox.com/s/3o0ktcacy7...Database.accdb

Please let me know if there are any issues accessing it.

The query name is qryVacantRooms.

Many thanks,

Shaun