Please help! - nasty query problem

My database consist three tables; Booking, Rooms and Customers.

Booking has
BookingID
CustomerID
RoomID
Arrival
Checkout

Rooms has
RoomID
RoomNum

Customers has
CustomerID
Lastname
Firstname

I have made queries based on this article: How to Query a Reservation Database by Date for Room Availability http://support.microsoft.com/?kbid=245074

I want to include customers Lastname,Firstname and RoomNum in the following query which returns a list of all the rooms that are already booked for given date.

PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout
date] DateTime;
SELECT [Booking].[Room], [Booking].[Arrival], [Booking].[Checkout]
FROM Booking
WHERE ((([Booking].[Arrival]) Between [Please enter arrival date] And
[Please enter checkout date]-1))
Or ((([Checkout]-1) Between [Please enter arrival date] And [Please
enter checkout date]))
Or ((([Booking].[Arrival])<[Please enter arrival date]) And
(([Checkout]-1)>[Please enter checkout date]-1));

How do I do that?

Thanks

Open the query in Design View.
Add the Customers table to the query (right-click and select Show Tables...)
Link the CustomerID fields in the two tables.
Drag the FirstName and LastName fields to two columns.
.

Thanks

Of course. Any idea how to display days between bookings? If i would like to see these not booked days instead of seeing a list which have booked rooms and days. I mean a query/(VBA-code?) which returns a list of all the available rooms and available days. This would be very helpful.

This query will list All the Rooms and Days Available for Booking:

SELECT Booking.Room, Booking.Checkout AS AvailableFrom,
(Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) AS AvailableTo
FROM Booking
WHERE Booking.Checkout >=Date() and Booking.Checkout <> (Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) or
(Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) is null
ORDER BY Booking.Room, Booking.Checkout;


Note
As a subquery is used, running it will take time if the table is large.
.

Thanks Jon!

The query works fine but let's say I have a room booked for 9-10.9 and 16-17.9. And because today is 13.9 query returns these AvailableFrom(17.9) and AvailableTo(until the next booking) values ok, but it doesn't show that I have room available from 10.9-16.9.

So the problem is that query doesn't display the first free booking period. The following available days instead are displayed correctly.

Try this one:

SELECT [Booking].[Room], [Booking].[Checkout] AS AvailableFrom,
(Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) AS AvailableTo
FROM Booking
WHERE (Booking.Checkout<>(Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) AND (Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout])>Date()) OR (Select Min([Arrival]) from [Booking] as S where S.[Room]=Booking.[Room] and S.[Arrival]>=Booking.[Checkout]) Is Null
ORDER BY [Booking].[Room], [Booking].[Checkout];
.

Ok, that solved my question. Now it does the trick.

Thanks very much for your help Jon.