Simple query problem

[rajnag]

Hi everyone,



This is a pretty straightforward problem but I can't seem to wrap my head around it. I'm pretty much a noob when it comes to Access so bear with me!
I've got a simple database for a hotel room booking system with 3 tables as below:

tblCustomer:
CustomerID
Forename
Surname
etc

tblBooking:
BookingID
CustomerID
RoomID
BookingDate

tblRooms:
RoomID
RoomNumber
RoomType
RoomCost

The requirements of the system are pretty basic, each customer who makes a booking only stays for one night at a time in one room.

What I'm trying to do is set up a way for the user to specify a date, and then have the system return which rooms are free (unbooked) on that date.

I've got some idea that I need to use a query to do this. I understand how to get it to show me rooms which are booked on that day, just not rooms which are NOT booked on that day.
I've been banging my head against a wall on this one, can someone please point me in the right direction?

Thanks!

[NTC]

its not as simple as it sounds. if your Booking tbl has a record of every single date then your basic date query finds those rooms which are booked.

Call this query1.

Make another new query, use the 'Query Wizard' and look for the 'Find Unmatched Query Wizard'....follow the wizard carefully and compare between query1 and Rooms table.

[Coder]

Thanks for posting this, as I am a little new to access, I have just joined because I start a new class at school in 2 weeks that involves this same exact stuff. I purchased 2 books, Access 2007 VBA and Access 2007 Programming as the teacher instructed to do for help with examples.

If these books don't help me, than I hope I can get help here at this forum, since it is obvious that there are some experienced people that have enough knowledge to help solve problems with simple navigation, and the like in Access.

[weekend00]

Create a query "RoomBooked" as:

SELECT tblBooking.RoomID FROM tblBooking GROUP BY tblBooking.RoomID, tblBooking.BookingDate HAVING (((tblBooking.BookingDate)=[WhichDate]));

Create another query "RoomNotBooked" as:

SELECT tblRooms.* FROM tblRooms LEFT JOIN RoomBooked ON tblRooms.RoomID = RoomBooked.RoomID WHERE (((RoomBooked.RoomID) Is Null));

Run query "RoomNotBooked", it requires to type in a date, and then vacant rooms information will show.

[rajnag]

Create a query "RoomBooked" as:

SELECT tblBooking.RoomID FROM tblBooking GROUP BY tblBooking.RoomID, tblBooking.BookingDate HAVING (((tblBooking.BookingDate)=[WhichDate]));

Create another query "RoomNotBooked" as:

SELECT tblRooms.* FROM tblRooms LEFT JOIN RoomBooked ON tblRooms.RoomID = RoomBooked.RoomID WHERE (((RoomBooked.RoomID) Is Null));

Run query "RoomNotBooked", it requires to type in a date, and then vacant rooms information will show.

Thank you so much, it works beautifully!