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

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.

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

2. Populate the fields of the Booking table with the following data:

   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

3. 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.
4. Populate the fields of the Rooms table with the following data:

   ID	RoomNum
   1	101
   2	102
   3	103
   4	104

5. In the Database window, click Queries under Objects, and then click New.
6. In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click close.
7. 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.
8. Save the new query as Rooms Reserved, and then close the query.
9. Create another query by clicking Queries under Objects, and then clicking New.
10. In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click close.
11. 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.
12. Save this query as Rooms Available.
13. 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.