How Do I Create a Database to Track Reservations?

[rogern]

I'm trying to create a cabin rental reservation system in Access. I'm not sure how to keep track of dates reserved and dates available for each rental unit. There are only four rental units and availability dates start on Memorial Day and end on Labor Day. So far I have identified a need for at least 3 tables: Reservation, Guest, Unit. Do I need to create a table called Availability that contains all available dates for each rental? If so, how do I easily populate that table? How could I generate a report of available dates by unit?

I would appreciate any hints or tips on how I could implement this rental tracking system.

Thanks

 

[pgk]

Hi,
You could have a table that contains the unit name, date and availability status.

A few typical records could be:

Unit1 08/23/2002 R
Unit1 08/24/2002 A
Unit1 08/25/2002 A
Unit2 08/23/2002 A
Unit2 08/24/2002 R

and so on.

The table contains the unit names, all the dates on which the unit will be rented out ( in your case, all dates between Memorial Day and Labour Day ) and the status of the unit for that date ( R- Rented; A- Available ).

In the form where you rent out the units, make sure that the status for the particular unit-date is changed to 'R'. After check out of the occupants, change the status to 'A'.

The unit name - date combination can act as a composite primary key.

Hope this gives you some idea as to how to proceed. Post if you want anything else.
With regards,
PGK

 

[rookiedev]

Access has a resource scheduler that will get you started. Use the database wizard and it will get you started. You can modify it to fit your needs. I use it to schedule our workers and it works out great.

Good Luck!
RookieDev

 

[rogern]

Thanks PGK and RookieDev for your responses.

Here's a little more detail on the tables I am considering.

I have decided upon 3 tables so far:

RESERVATION
ReservationID (autonumber)
ArriveDate
DepartDate
UnitID
NumNights (calculated)
NumPersons
GuestID

GUEST
GuestID (autonumber)
FirstName
LastName
Street
City
State
Zip
Country
Phone
Email

UNIT
UnitID (autonumber)
UnitName
UnitRate

I am still in the planning / brainstorming phase and haven't put anything into Access yet. The main problem for me is how to keep track of the dates and their availability.
Do I need another table such as the following? (I think you suggested this, PGK):

AVAILABILITY
AvailabilityID (autonumber)
Date
UnitID
Reserved (boolean)
ReservationID

If so, I'm not sure how to design queries or forms to get what I need. I'm still pretty new to this. If I use a table of available dates, is there a way to somehow autopopulate that table? How can I best indicate when a date is reserved by a specific unit? When cancellations occur, how is the databse updated to show the date available again? When generating an availability listing, the query should only consider dates from "Now" to the end of the rental season. The query should also show availability by rental unit.

These are just some of the questions I have as I think about designing this tracking system.

PKG, you mentioned using a composite key. How does that work?

Thanks again for your input.

Bob

 

[pgk]

Hi,
As per my idea, you will need the fourth table. I don't think you require the AvailabilityId field and ReservationId field. The Availability tabel would look like this:

Table : Availability

dateChecked Date
unitId Long
reserved Yes/No


Auto populating the availability table:

You could do this using a command button on the main form ( called AutoPopulate ). Let the user enter the two dates between which the units will be available for rent. This will give more flexibility. ( Though right now the units are rented out only between Memorial and Labour days, it might change in the future. hence the flexibility ). When the comman button is pressed, add new records for each of the dates between the the two dates and set the Reserved field to No.

Whenever you reserve a unit for a particular date(s), calculate the dates on which it is reserved and set the reserved field to Yes for that particular Date/Unit combination. Eg. if unit1 with unitId 001 is reserved for two days ( 08/25/2002 & 08/26/2002 ), the Availability table would look like:

001 08/24/2002 No
001 08/25/2002 Yes
001 08/26/2002 Yes
001 08/27/2002 No

When the guest checks out or cancels, change the Reserved field to No.

This combination of date and unit number is called a composite key since you are able to uniquely identify a record in the Availabilty table.

Hope this helps. Let me know if you need anything else.
With regards,
PGK