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

Article translations Article translations
Article ID: 245074 - 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.

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:
    Collapse this tableExpand this table
    IDRoomArrivalCheckout
    11019/6/999/10/99
    21019/12/999/17/99
    31029/2/999/9/99
    41039/1/999/2/99
    51049/2/999/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:
    Collapse this tableExpand this table
    IDRoomNum
    1101
    2102
    3103
    4104
  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.

REFERENCES

checkin checkout reserving hotel motel

Properties

Article ID: 245074 - Last Review: August 27, 2002 - Revision: 1.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto KB245074

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com