By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Query to find available rooms

P: 14
thanks again for the quick and excellent response to my last question.

Im nearly finished my mini project now and have got stuck on a way to print out a list of rooms that are not currently being booked.

I have a table
tblbooking which has in it a booking id (Auto number), cutomer ID and RoomID (from the tblroom table)
And tblroom which has RoomID roomPrice and Room Type

and what i want to do is run a query that will search though the current bookings and print a list of all rooms not currenlty booked? Ive tried a few things but it doesnt seem to work.

Any idaes

thanks

Mark
Dec 11 '06 #1
Share this Question
Share on Google+
15 Replies


100+
P: 143
What determines that thay are not currently booked?
Dec 11 '06 #2

NeoPa
Expert Mod 15k+
P: 31,660
The table MetaData would probably help here.
Please post it in Code tags to keep the layout clean.
Expand|Select|Wrap|Line Numbers
  1. Table Name=
  2. ID; Autonumber; PK
  3. FieldName1; Numeric
  4. FieldName2; String
  5. etc
Dec 11 '06 #3

ADezii
Expert 5K+
P: 8,669
thanks again for the quick and excellent response to my last question.

Im nearly finished my mini project now and have got stuck on a way to print out a list of rooms that are not currently being booked.

I have a table
tblbooking which has in it a booking id (Auto number), cutomer ID and RoomID (from the tblroom table)
And tblroom which has RoomID roomPrice and Room Type

and what i want to do is run a query that will search though the current bookings and print a list of all rooms not currenlty booked? Ive tried a few things but it doesnt seem to work.

Any idaes

thanks

Mark
'I'm making the assumption that there is a 1 to MANY Relationship between
'tblRoom and tblBooking based on [RoomID] namely:
'tblRoom.[RoomID](1) ===> tblBooking.[RoomID](M). If this assumption is
'correct then what you need is an Unmatched Query which will simply give you
'all the Rooms in tblRoom which do not have matching entries in tblBooking
'which in my mind translates to Rooms with no current Bookings. Here is the
'Procedure:

1) Query, New, Unmatched Query Wizard
2) First prompt - tblRoom
3) Second prompt - tblBooking
4) Select [RoomID] as the matching Field in both Tables
5) Select all Fields in the output results, namely [RoomID], [RoomPrice], and
[Room Type]
6) Name the Query e.g. qryRoomWithNoBookings

NOTE: I hope I interpreted your request properly, if not I apoligize.
Dec 11 '06 #4

P: 14
Sorry for delay in responding

ADezii

you are great!

thanks that is spot on!
Is there an easy way i can modify that to only look at a certain day/date?
In the bookings table i have a field Bookingstartdate? I tried >date() but that doesnt work. I understand why it doesnt work but not how to fix it!

many thanks

Mark
Dec 13 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
Open the query in design view and add the field 'BookingStartDate' to the fields shown (double-click on it from its table) then clear the 'Show' tick and add '>Date()' in the criteria (assuming you want to select only those records after today).
Dec 14 '06 #6

P: 14
Open the query in design view and add the field 'BookingStartDate' to the fields shown (double-click on it from its table) then clear the 'Show' tick and add '>Date()' in the criteria (assuming you want to select only those records after today).

cant get that to work it just doesnt give me any results.
It works fine normally but as soon as i introduce date it stops working.

any other ideas?

thanks

Mark
Dec 19 '06 #7

P: 14
cant get that to work it just doesnt give me any results.
It works fine normally but as soon as i introduce date it stops working.

any other ideas?

thanks

Mark
my sql is

Expand|Select|Wrap|Line Numbers
  1. SELECT tblrooms.Room_Number, tblrooms.Room_Type, tblrooms.Room_Price_Id, tblrooms.Additional_Notes
  2. FROM tblrooms LEFT JOIN tblbooking ON tblrooms.Room_Number = tblbooking.Room_Number
  3. WHERE (((tblbooking.Room_Number) Is Null));

when i add date

Expand|Select|Wrap|Line Numbers
  1. SELECT tblrooms.Room_Number, tblrooms.Room_Type, tblrooms.Room_Price_Id, tblrooms.Additional_Notes
  2. FROM tblrooms LEFT JOIN tblbooking ON tblrooms.Room_Number = tblbooking.Room_Number
  3. WHERE (((tblbooking.Room_Number) Is Null) AND ((tblbooking.Booked_Start_Date)>Date()));
It doesnt produce any results
Dec 19 '06 #8

NeoPa
Expert Mod 15k+
P: 31,660
Do you have any records in your table where the tblbooking.Booked_Start_Date is greater than today?
Is tblbooking.Booked_Start_Date a DateTime field?
Dec 19 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
Forget that!
The logic of your query is upside-down. Give me a while to get home and I'll look into what you actually want. It doesn't make sense to say 'Show me all items without a matching booking - oh and while you're about it the matching booking must be for after today'!
Dec 19 '06 #10

NeoPa
Expert Mod 15k+
P: 31,660
If you want to select only those rooms where there is a booking for after today, rather than those where no bookings exist then remove the (((tblbooking.Room_Number) Is Null) AND bit.
If you want those rooms that are either after today or don't exist at all, then change the AND to an OR.
Dec 19 '06 #11

P: 14
When i change it to an or it gives me rooms not booked or any rooms booked after today.
Maybe im approaching this from the wrong angle?
All i want to do is be able to print out a list of rooms that do not have a booking for say the next week.
Dec 20 '06 #12

NeoPa
Expert Mod 15k+
P: 31,660
Try (specifically for the next week one) :
Expand|Select|Wrap|Line Numbers
  1. WHERE (Nz(tblbooking.Booked_Start_Date,#1/1/1900#) Not Between Date() And Date()+6)
Dec 20 '06 #13

P: 14
Try (specifically for the next week one) :
Expand|Select|Wrap|Line Numbers
  1. WHERE (Nz(tblbooking.Booked_Start_Date,#1/1/1900#) Not Between Date() And Date()+6)
thanks for your help NeoPa but it still brings back 0 results.
I tried doing a 'weekly booking query' first then an unmatched query but that didnt work either.

Think i might give up!
Dec 20 '06 #14

P: 14
Forget that!
The logic of your query is upside-down. Give me a while to get home and I'll look into what you actually want. It doesn't make sense to say 'Show me all items without a matching booking - oh and while you're about it the matching booking must be for after today'!
Ive got a headache!

Am i going about this the wrong way?

essentially all i want to do is provide a list of non booked rooms so that the person on reception can see which are available. Would it be easier to do some coding withing the booking form? For example to only display the rooms that are free during that date period?

the current code i have before update is.


If Me.NewRecord = True Then
Dim strWhere As String, strmessage As String

strWhere = "((Room_Number=" & Me!Room_Number & _
") AND (Booked_End_Date>=#" & _
Format(Me!Booked_Start_Date, "m/d/yyyy") & _
"#) AND (Booked_Start_Date<=#" & _
Format(Me!Booked_End_Date, "m/d/yyyy") & _
"#))"

Set rsClone = Me.RecordsetClone
rsClone.MoveFirst
rsClone.FindFirst strWhere


If rsClone.NoMatch Then
MsgBox ("Booking accepted.")

Exit Sub
Else: MsgBox ("You are trying to book a room for a date that it is already booked for.")
Cancel = True
End If

End If

this code Looks to see if the currently selected room is booked. Could i modify this is some way to populate the Room number combo box with available rooms after a date has been selected?

Hope that makes sense!
Dec 20 '06 #15

NeoPa
Expert Mod 15k+
P: 31,660
Mark,
What I'm trying to get you to do is to test things so that we can see where the problem might be. This requires tidyness and precision.
To test the code in my previous post you must use the code in my previous post. If you tell me it hasn't produced any results then I'm thinking that my code hasn't but the code you post is markedly (excuse pun) different.
We need to determine what is going wrong before we look at fixing it. That's why we experiment and, depending on the results, we look to see how we can fix it.
Please let me know if this makes sense to you and, if so, what your results were with the code I posted (exactly).
Dec 28 '06 #16

Post your reply

Sign in to post your reply or Sign up for a free account.