Query to find available rooms

[markymark34]

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

[Tanis]

What determines that thay are not currently booked?

[NeoPa]

The table MetaData would probably help here.
Please post it in Code tags to keep the layout clean.

Table Name=
ID; Autonumber; PK
FieldName1; Numeric
FieldName2; String
etc

[ADezii]

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. qryRoomWithNoBo okings

NOTE: I hope I interpreted your request properly, if not I apoligize.

[markymark34]

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 Bookingstartdat e? I tried >date() but that doesnt work. I understand why it doesnt work but not how to fix it!

many thanks

Mark

[NeoPa]

Open the query in design view and add the field 'BookingStartDa te' 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).

[markymark34]

Open the query in design view and add the field 'BookingStartDa te' 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

[markymark34]

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

SELECT tblrooms.Room_Number, tblrooms.Room_Type, tblrooms.Room_Price_Id, tblrooms.Additional_Notes
FROM tblrooms LEFT JOIN tblbooking ON tblrooms.Room_Number = tblbooking.Room_Number
WHERE (((tblbooking.Room_Number) Is Null));

when i add date

SELECT tblrooms.Room_Number, tblrooms.Room_Type, tblrooms.Room_Price_Id, tblrooms.Additional_Notes
FROM tblrooms LEFT JOIN tblbooking ON tblrooms.Room_Number = tblbooking.Room_Number
WHERE (((tblbooking.Room_Number) Is Null) AND ((tblbooking.Booked_Start_Date)>Date()));

It doesnt produce any results

[NeoPa]

Do you have any records in your table where the tblbooking.Book ed_Start_Date is greater than today?
Is tblbooking.Book ed_Start_Date a DateTime field?

[NeoPa]

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'!

[NeoPa]

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.R oom_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.

[markymark34]

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.

[NeoPa]

Try (specifically for the next week one) :

WHERE (Nz(tblbooking.Booked_Start_Date,#1/1/1900#) Not Between Date() And Date()+6)

[markymark34]

Try (specifically for the next week one) :

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!

[markymark34]

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_Dat e>=#" & _
Format(Me!Booke d_Start_Date, "m/d/yyyy") & _
"#) AND (Booked_Start_D ate<=#" & _
Format(Me!Booke d_End_Date, "m/d/yyyy") & _
"#))"

Set rsClone = Me.RecordsetClo ne
rsClone.MoveFir st
rsClone.FindFir st 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!