Guest House Reservations database

[jfrick]

My database consist of 4 files; reservations, house, guest and calendar. I want a portal showing how many rooms (of 22) from the house file are available.

My reservations file contains arrival and departure date fields and a some calc fields that create a date range and room info.

I currently enter a date into a global date field that's related to the reservations file(many dates and rooms)which gives inaccurate results and can list the same room more that once. I think I needed another line items type file but I'm not sure. Hope I've given enough info. Can anyone help?

[LiveOak]

Too much question, too little information...

What is a record in your reservation file?

What is a record in your house file?

Do you just want a single NUMBER showing room availability or do you want a LIST of rooms in a portal ("I want a portal showing how many rooms (of 22) from the house file are available")?

Your answer should have the form: "A records is a reservation for one guest one room for one day" or "A record is a reservation for one guest for one or more rooms for one or more days" etc.

-bd

[jfrick]

Answer: a LIST of rooms in a portal ("I want a portal showing how many rooms (of 22) from the house file are available") A record is a reservation for one (or more) guest(s) one room for one day or more(I've already figured out how to handle a range of dates.

If the user enters a date such as 8/4/2001 then I want to check the reservation file ArrivalDate and DepartureDate fields to see if 8/4/2001 falls between them.

Reservation file fields consist of: ReservID (key), HouseID, GuestID, ArrivalDate, DepartureDate, CalcDateRange, (from House file: CalcRoom#,CalcPhone, CalcBed, CalcBath). Calendar and Guest files at this point aren't important.

[LiveOak]

Ok, I'll guess! A record in the house file is a room?

The problem you have is you want to display the dates NOT reserved. To do this you must have an entry in some file for every room for every possible date (22*365 per year), and then break the key (or remove the record) when the room is reserved.

If you try to use the house file and make the key on the house file conditional on date in the reservation file based a reservation, it won't work, as the field used on the right side of a relationship can't be indexed.

As an alternative you can create the file of room/days (22 x 365 x years ahead), fill it ahead with all dates for all rooms, and change a field in this file (Avail?) using a script run from the reservations file. This "Avail?" field would be part of an composite key, maybe defined as:

Avail? (text) <--- value list "Yes", "No"

AvailDate (calculation, text, indexed) = Avail? & Date

In the main file (whichever the portal is in)

Enter the date into a global:

gDate (date)

and calculate a key field to used in the relationship with the file of room/days.

key (calculation, text, indexed) = "Yes" & Date

Base the portal upon the relationship between "key" in you main file and "AvailDate" in the room/date file.

-bd

[ August 04, 2001: Message edited by: LiveOak ]

[jfrick]

Thank's LiveOak, That's all I needed. Sorry it took me a few tries to get you the necessary info - what can I say - I'm a newbie.

[LiveOak]

Definitely not a good first project for a newbie, a complicated design.

-bd

[ August 06, 2001: Message edited by: LiveOak ]

[jfrick]

Hey LiveOak, I've got it working but I wondering if there's any way my portal relationship can use a range or list of dates (arrival date thru departure date) in place of my gDate field. Thanks, jf

[BobWeaver]

You can do a multiple key on a relationship by putting all of the applicable values in the key field separated by

[jfrick]

Thanks Bob, That's exactly what I want to do. I love this place.