Microsoft KB Archive/245074

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

Article ID: 245074

Article Last Modified on 8/27/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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.



MORE INFORMATION
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:  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:  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.</li>  On the View menu, click SQL View, and then type the following SQL statement in the SQL window: <pre class="fixed_text">  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. </li> Save the new query as Rooms Reserved, and then close the query.</li> Create another query by clicking Queries under Objects, and then clicking New.</li> In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click close.</li>  On the View menu, click SQL View, and then type the following SQL statement in the SQL window: <pre class="fixed_text">  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. </li> Save this query as Rooms Available.</li> 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.</li></ol>

<div class="references_section">