Hotel Availability

[masterdaave]

I am working on a Hotel booking system project.



For simplicity I have two tables:

tblBooking: bookingID, roomNum*, checkIn, checkOut
tblRooms: roomNum, roomType, cost

I want to search the booking table with a check in and check out date and return all the rooms that are available.

I have tried querying and creating some outer join solutions but I am terrible with joins/unions and cannot get near a solution although I feel it shouldn't be too hard.

I appreciate any help people can offer.

[orange]

Thee is a free data model here that may meet some of your needs, or give you some ideas.

[masterdaave]

Thee is a free data model here that may meet some of your needs, or give you some ideas.

Hi,

sorry but that doesn't really help. I have already set up my data model. What I am struggling to do is check availability of a room.
EXAMPLE
I need a list of rooms that are available at this date. If the booking table has the following data:

bookID, rmNum, checkIn, checkOut
1..........1..........26/1..........27/1
2..........1..........29/1..........1/2
3..........1..........5/2............6/2
4..........2..........24/1..........26/1

and the room table has the following data
RmNum, Type
1............Double
2............Double
3............Single

Searching for a room between the dates of 28/1 and 30/1 should return rooms 2 and 3.

[orange]

Show readers:
- a jpg of your data model
- your business rules


Which numbers represent Rooms/Records?

See if this helps

[masterdaave]

I actually managed it with the following queries:

qryAllRooms
SELECT *
FROM bookings;

qryAvailableBookings
SELECT booking.ID, booking.start, booking.end, booking.roomNum
FROM booking
WHERE (((booking.start)<#1/4/2013# And (booking.start)<#1/6/2013#) AND ((booking.end)<#1/4/2013# And (booking.end)<#1/6/2013#)) OR (((booking.start)>#1/4/2013# And (booking.start)>#1/6/2013#) AND ((booking.end)>#1/4/2013# And (booking.end)>#1/6/2013#));



qryNotAvailable
SELECT *
FROM qryAllRooms
WHERE ID NOT IN (SELECT ID FROM qryAvailableBookings);


qryAvailable
SELECT *
FROM rooms
WHERE roomNum NOT IN (SELECT roomNum FROM qryNotAvailable)