vacant rooms

[quasimodo]

i'm making a hotel booking/billing system for my A-Level ICT coursework.

I'm having trouble making up a query to show a list of vacant rooms. I've attached a pic of my tables and fields...

When a customer comes to the hotel, they are added to the customer table (if new) or their record is brought up. Their ID and the room they stay in is entered into the guest bill table. when a guest cheks out, a check-box is checked to show that they have checked out.

I need a query that uses this information to give a list of all vacant rooms. I've tried having the criteria for the checkbox as false, but this only brings up the rooms that have people in them already (ie entrys on the GuestBill table that are currently at the hotel), not the vacant ones....

I dont want to have to add a new field in the rooms table which will have to be changed manually.

I would greatly appreciate any help.
Thanks in advance.

 

[Rich]

Try searching here for Bookings, I'm sure this problem's been discussed before

 

[spacepro]

when a guest cheks out, a check-box is checked to show that they have checked out.

I've tried having the criteria for the checkbox as false

If you want a list of vacant rooms, then don't you want the criteria as - True-, as this would tell you who as checked out, and therefore the room being vacant, and not people who haven't checked out as their rooms are still occupied.

Andy

 

[quasimodo]

thanks for the suggestion, but there are records for ALL customers that have stayed at the hotel, so if i have the criteria as TRUE, it will bring up a list of all previous guests who have stayed at the hotel.

I only want a list of all vacant rooms at any given time.

My tutors suggested adding an extra field in the Rooms table, which would have to be automatically updated when a room becomes vacant. I would like a way in which the system can automatically produce a list for the user to choose from, so they can check in a guest with minimal hassle.

 

[quasimodo]

Try searching here for Bookings, I'm sure this problem's been discussed before

Rich - I couldnt find the post you are talking about. Any chance of a direct link please??

 

[isxaa1]

I have the same problem with yours. Did you manage to resolve the issue yet? If yes, could you please help me?
Cheers

 

[Rich]

Here's just one post returned using the search facility here, there are many more
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=43907&highlight=Hotel

 

[quasimodo]

cheers rich....

you couldnt help me with another problem could u?
http://.....adid=44069

 

[quasimodo]

I have the same problem with yours. Did you manage to resolve the issue yet? If yes, could you please help me?

nup... i've managed to get a list of the rooms currently vacated, but i dont know how to get a list of vacant rooms from that list... so if anyone can help me with that, i'd be more than grateful...

 

[Rich]

Post a 97ver copy of your db here, make sure you compact it first

 

[quasimodo]

here it is so far....