I'm trying to make a query that will show what rooms in my hotel are available between 2 dates.
The 2 tables that will be used are:
Booking
book_room ------ foriegn key, room_no
date1 -------- Date of booking, each night of booking has a record
custno
Room
room_no
smoking -------- if room is smoking
bed ---------- Single, double, twin
So I have a form where the user enters arrival and departure dates, choice of room, smoking or none smoking. Then I want a query to see which rooms AREN'T booked during this period.
I am, at the momment working with the query:
But at the moment it is returning a booked room when the room is booked in the future. I need to some how tell it that if the room is booked between those dates then remove every instance of the room from the search.
Example:
room 101 is booked from 19/02/2004 to 22/02/2004. This is shown in the booking table by:
room no date custno
101 19/02/2004 1
101 20/02/2004 1
101 21/02/2004 1
So if i do a search for 20/02/2004 to 22/02/2004 It returns one of the bookable values as room 101 because there is a booking for it on the 19/02/2004.
I need a query that will say if there are any bookings between the dates then the room no is not available at all.
Can anyone help?
Emma
The 2 tables that will be used are:
Booking
book_room ------ foriegn key, room_no
date1 -------- Date of booking, each night of booking has a record
custno
Room
room_no
smoking -------- if room is smoking
bed ---------- Single, double, twin
So I have a form where the user enters arrival and departure dates, choice of room, smoking or none smoking. Then I want a query to see which rooms AREN'T booked during this period.
I am, at the momment working with the query:
Code:
SELECT DISTINCT room.room_no, room.smoking, room.bed, booking.date
FROM room LEFT JOIN booking ON room.room_no = booking.book_room
WHERE (((room.smoking)=[Forms]![avail]![smoke]) AND ((room.bed)=[Forms]![avail]![bed]) AND ((booking.date) Not Between [Forms]![avail]![date1] And [Forms]![avail]![date2])) OR (((room.smoking)=[Forms]![avail]![smoke]) AND ((room.bed)=[Forms]![avail]![bed]) AND ((booking.date) Is Null));
But at the moment it is returning a booked room when the room is booked in the future. I need to some how tell it that if the room is booked between those dates then remove every instance of the room from the search.
Example:
room 101 is booked from 19/02/2004 to 22/02/2004. This is shown in the booking table by:
room no date custno
101 19/02/2004 1
101 20/02/2004 1
101 21/02/2004 1
So if i do a search for 20/02/2004 to 22/02/2004 It returns one of the bookable values as room 101 because there is a booking for it on the 19/02/2004.
I need a query that will say if there are any bookings between the dates then the room no is not available at all.
Can anyone help?
Emma