Article ID: 245074 - Last Review: August 27, 2002 - Revision: 1.0

ACC2000: How to Query a Reservation Database by Date for Room Availability

View products that this article applies to.

This article was previously published under Q245074

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

SUMMARY

If you have a reservations database, you may need to query it to determine which rooms can be booked for a range of days. This article presents an example that demonstrates a way to do this.

Back to the top

In this example, the first day of the booking period can be the checkout day for another reservation, and the last day of the booking period can be the arrival day of another reservation. The result must return only the rooms that can be booked for a new reservation.

Create the following table, and name it Booking:
Table: Booking
--------------------
Field Name: ID
Data Type: Counter

Field Name: Room
Data Type: Number

Field Name: Arrival
Data Type: Date/Time

Field Name: Checkout
Data Type: Date/Time
Populate the fields of the Booking table with the following data:
Collapse this tableExpand this table
ID Room Arrival Checkout
1 101 9/6/99 9/10/99
2 101 9/12/99 9/17/99
3 102 9/2/99 9/9/99
4 103 9/1/99 9/2/99
5 104 9/2/99 9/6/99
Create the following table, and name it Rooms:
Table: Rooms
----------------------------
Field Name: ID
Data Type: Counter
Indexed: Yes (No Duplicates)

Field Name: RoomNum
Data Type: Number
NOTE: This table simply contains all the room numbers.
Populate the fields of the Rooms table with the following data:
Collapse this tableExpand this table
ID RoomNum
1 101
2 102
3 103
4 104
In the Database window, click Queries under Objects, and then click New.
In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click close.

On the View menu, click SQL View, and then type the following SQL statement in the SQL window:

   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));

Note that by subtracting 1 day from the booking request checkout date, you can checkout on the same day as another existing reservation is arriving.

By subtracting 1 day from the reservation checkout date, you can book a date that is the last day of an existing reservation.

This query returns a list of all the rooms that are already booked for this date range.

Save the new query as Rooms Reserved, and then close the query.
Create another query by clicking Queries under Objects, and then clicking New.
In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click close.
On the View menu, click SQL View, and then type the following SQL statement in the SQL window:
```
   SELECT Rooms.RoomNum
   FROM Rooms LEFT JOIN [Rooms Reserved] ON Rooms.RoomNum = [Rooms
   Reserved].Room
   WHERE ((([Rooms Reserved].Room) Is Null));
```
This query returns a list of all the rooms that are available based on the Rooms table and the Rooms Reserved query.
Save this query as Rooms Available.
Run the Rooms Available query with booking request dates of 9/2/99 - 9/6/99. The query returns rooms 101 and 103. These are the only rooms that meet the requirements specified in this example.