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 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? How can I track which dates are taken? How can I mark a range of dates as taken if given a begin and end date?

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

Thanks

 

[Darrylles]

Hey Rogern,

I just know that you can do it, the tables are A1 - your relationships are probably A1.

Care to shown them to us?

Individual dates for each unit are crucial (because each unit may well be let on a day-by-day basis).

Ranges of dates would then have to be retrieved via a query, (certain ranges you may well want to 'protect' so that they can be booked in their entirety).
These would be 'protected' by a query.

I feel that the tables that you have are totally sufficient to give you the info and 'control' that you need.

Regards,

Darrylle





Regards

Darrylles "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[rogern]

Thanks for your reply, Darrylle.

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?:

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.

Thanks again for your input.

Bob