Hotel Room Availability Query

[JohnOwens111]

Hi Everyone,
hope someone out there can figure this one out.

• I am developing an access database and forms based on a hotel booking system.
• There is a tblRoomState table, which holds the roomID, the date and the state of the room, (so for a 3 night stay, this table holds three entries).
• I have a query which returns any roomID's from the tblRoomState table which have a state value of 1 at a specified date.
• I want to run another query which returns roomID's from the tblRoom table which are not part of the recordset of the first query.

Since there is no records held in the database for rooms which are not booked, the only way to access "availability" is to use a recordset of rooms which are booked and test against that.

Thanks,
John

 

[edtab]

Not sure if I understood your problem correctly, but it
looks straight forward.

To filter out records available as at a certain date,
set the condition on the state field to: <>1; and of
course the date you are interested in.

 

[JohnOwens111]

The problem is that there are no records for rooms which do not have a state <> 1.

Basically, i have a table called tblRoomState which only will have a roomID in it, if there is a booking for that room. There is no record of state in the tblRoom table. So first i need to create a set of "booked rooms" from the tblRoomState table, and then run a query of ALL rooms using the tblRooms table, and remove the "booked rooms".

 

[raskew]

Hi -

Take a look at Allen Browne's 'Clashing Events' solution: http://allenbrowne.com/appevent.html

HTH - Bob

 

[GaryPanic]

would not the solution be a default value of 0

so room 111 default value 0 - empty
if booked value 1

you would end up with a huge table with 365 days or fields in (one per day)
and they would all be 0 to begin with as they get book you would end up with a 1 in a date range

does depend on how many rooms
lets say 50 rooms
you could then have a look at range of say 14th june
14/06/07 and your screen would have all the rooms shown that are 0 or 1 or both i would recommend that you have say 5 days greater than you entry date shown so you would see a view of 14 june to 19 june
you could make your form tidier then and have tick boxes or enter room no and date range and it updates the table straight away

 

[GaryPanic]

Also why reinvent the wheeel - there is a sample of a room booking system in the samples (I have not looked at it )

 

[JohnOwens111]

raskew.

1. GaryPanic, that method would require a default entry for every room for every date from now until the end of time (or as far as time extends in access) since each room is in a not booked state for every date possible until someone books it. And if that hotel system in the sample was what i wanted, i wouldnt be asking questions trying to develop my own on this forum.

2. raskew, the comparison of dates reflecting room availability for a multiple night stay is a step ahead of where i am.

I am retrieving a set of "booked rooms" using a query for roomID's from the tblRoomState table.

I want to make a new query, which returns all roomID's from a tblRoom table, such that the roomID is NOT contained in the result set of the previous query.

That's all.

 

[Fear Naught]

I sounds like you need a sub query in the conditions part of your main query.

I don't think GaryPanic will be too happy with the tone your comments to him in your previous posting. He was after all only try to help you :-(

 

[edtab]

I see what you mean.

Try creating another query using the query wizard.
Choose the "Find Unmatched Query Wizard".

This should allow you to use the earlier query you created
to be compared against your tblRoomState table. The result
should be entries in tblRoomState that do not appear in
the earlier query.

I think this will give you what you want.

 

[The_Doc_Man]

First, this comes up so often that it HAS to be a class assignment.

Second, use the SEARCH function of this forum for "Bookings" and "Schedules" to find probably dozens of threads discussing the problem.

Third, you are correct that you need a list of entries against which to compare, but one of the inputs could be a parameter (see Parameter Queries). The other input could be just a map of rooms and their properties such as bed type (single, twin, queen, king), room type (closet, room, family room, suite), other attributes (smoking, view of cemetery, view of trash dump, view of other rooms, etc.) OK, got a little sarcastic there...

Because Access is a sparse system, your best bet is make a trial booking and look for conflicts (which DO exist) rather than spinning your wheels trying to find the empty spot first. Either way, you run into a set of loops, so some multi-layer filtration queries or some VBA will be in order.