06-06-14, 15:35 #1Registered User
- Join Date
- Jun 2014
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.
06-09-14, 05:13 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
try using a sub query and the 'not exists' clause
Microsoft Access tips: Subquery basics
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