Hi Schoffio,

There is a great room reservation program created by CobaltSky that he
posted on another forum. I have used the exact same principle for other
reservations.

]http://www.fmforums.com/threads/showflat.php?Cat=&Number=61669&pag-
e=&view=&sb=5&o=&fpart=2&vc=1[/url]

It's the seventh post down, by CobaltSky - just click the ATTACHMENT.
If you need to be a member to download, the membership is free.

LaRetta

--
Posted via http://dbforums.com

superfee wrote:
A reservation & registration system could be constructed as follows:

Three related files, one for rental units (let us suppose hotel rooms),
one for customers (guests), and one for reservations and registrations
(called hereafter the registration file).

The rental unit file would have a field for unit identifier, which must
be unique, and other fields for any other particulars of the unit, such
as number of beds, maximum occupancy, smoking or not, prices depending
on occupancy, etc. Each unit would have a record in this file.

The customer file would have a field for customer ID, which should be a
unique, computer-generated serial number, and fields for name, address,
phone number, etc. If you set up separate name fields for title, first
name, last name and suffix, it would also be useful to have a
calculation field that concatenates the name parts into a single field.
Each customer would have a record in this file.

The Registration file would have fields for date reserved, date
reservation made, unit ID, customer ID, number in party, price,
discount, net price, tax, amount, amount paid, payment method, etc. It
would also be useful for billing purposes to have a registration ID that
is calculated by concatenating customer ID and date reservation made.
Suitable fields could be then be defined for calculating bill amount
based on a self-join in this file based on reservation ID. Each unit
would have a record for each day it is reserved in this file. It would
also be useful to allow creation of related Customer records from the
Registration file.

The Registration file would end up having a record for each unit for
each day that unit is reserved.

In the Registration file define relationships to the Customer file based
on Customer ID, and to the Units file based on Unit ID. Define value
lists for unit ID and Customer ID based on those files, and attach these
value lists to the unit and customer ID fields. Allow creation of
related customer records from the registration file.

In the Registration file, put fields from the related unit and customer
records, including unit description and prices, and customer information.

In the Units file, define a relationship to the Registration file based
on Unit ID. Specify that the related records be sorted by date. Allow
creation of related registration records from the Units file. Define a
portal from the registration file. In the portal, put fields for date,
and any other fields you want from the registration file. It would be
useful to allow deletion of portal records.

To find the inventory of available units by date, go to the units file.
Do a find based on the date field in the portal from the related
reservation records. The find request should be to omit records that
have reservation dates in the specified range (start date...end date).
All the units that are not already reserved for those dates will be in
the found set. You can of course do a more restricted find, by looking
for those that are non-smoking, have a certain number of beds, etc, and
omitting the ones already reserved for the desired dates.

If a customer decides to reserve an available room, you can just create
a portal record for each of the desired dates for that room by entering
the dates, one date for each portal row, then go to the related
reservation records and enter all the customer info. You can put a
pushbutton in the portal that will take you to the related reservation records.

If a customer cancels a reservation before arriving, the appropriate
reservation records should be deleted, so that the affected unit will no
longer be omitted from the find of available units. Likewise, if a guest
leaves before the end of the reserved stay, the reservation records for
the remaining unused days, should be deleted.

Periods of unavailability for other reasons, such as maintenance, would
be treated in the same way as a customer reservation.

It would be useful also to define a relationship from the customer file
to the registration file, and put a portal from the registration file
into the customer file, with fields for date, unit ID, etc, and allow
deletion of portal records. That would make it very easy to delete
cancelled days and units for that customer.

Hope this helps.

Bill