Double Booking Problem (1 Viewer)

[esymmonds]

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:

 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

 

[spacepro]

You could add a field called Booked - yes/no datatype, and set your form to update the checkbox automatically once a date aas been put against the room number. Then you could just return the rooms that are not booked between the two dates.

Hope this helps
Andy

 

[esymmonds]

but a record only appears in the booking table if it's booked. It's working so far as not returning booked rooms but it returns a room with a booking date out of the criteria that is actually booked.

I need to put that if the room is booked inside the criteria then no other instances can be returned.

It's quite difficult to explain

Emma

 

[spacepro]

Emma,

Any chance of a quick example db?

Andy

 

[esymmonds]

Hi Andy,

What I've done is taken out the bits you need and put them in a database. If you open form.avail put todays date and tommorows date in and select double. then open the query.

You'll see that it gives you, among others, rooms 108 and 109 but if you check the booking table those rooms are actually booked.

Emma

 

[esymmonds]

Hello All,

I have got my system to not make double bookings now! Thought I'd just post how I've done it in case there's anyone else who has problems with it. There may be an easier way to do this but I'm not that bothered cos mine works

First of all I have a query that finds which rooms ARE booked so I use:

SELECT [book_room], [date] FROM booking
WHERE ((date Between [Forms]![avail]![date1] And [Forms]![avail]![date2]-1));

Then I left join this information to my room table. This gives me all of the rooms along with any bookings that have been made within the criteria so if the feild is null the room is not booked within the days. so i used:

SELECT DISTINCTROW room.room_no
FROM room LEFT JOIN bookings ON room.room_no = bookings.book_room
WHERE (((room.smoking)=[Forms]![avail]![smoke]) AND ((room.bed)=[Forms]![avail]![bed]) AND ((bookings.book_room) Is Null));

So there it is. Thanks to everyone who looked at my thread.

Emma

 

[spacepro]

Glad you solved your problem.

Andy

 

[syth_2002]

Hey

Any chance i could see the fixed version or could someone tell me how that code was inserted, i havent a clue about databases and have this excalt same problem.

Thanks to any help