You need at least two tables for this: Facilities, Occupancy with this relationship:
Facilities::FacilityID = Occupancy::FacilityID AND
Facilities::DateField > Occupancy::ArrivalDate AND
Facilities::DateField < Occupancy::DepartureDate
Occupancy is one record for one group of guests linked to a specific facility.
This calculation efined in Facilities:
Sum ( Occupancy::NumberInParty )
Will compute the total occupancy for a given date you specify in Facilities::DateField.
You can define a Max Occupancy field in Facilities to compare to this total to check how close to maximum capacity a given facility is for a given date.
Many variations on this technique are possible depending on the structure of your database and the needs of your business.
Hi Phil and thanks for your quick response. Much appreciated. I do have a few questions and things I'd like to clarify. So here goes.
The Occupancy table you mentioned would be something we would call a booking instance I believe. What we have planned is to select a particular facility (lodge) from a pull-down menu list and key in the check in and check out dates there. As we have a several lodges dotted around the place we created a separate table called Lodges with the fields Name, Location and Capacity. This will then be used in a Value List to allow us to choose the lodges that a particular guest would like to stay at.
The field Facilities::Datefield in your solution sounds like a search field type Global. Yes? So hypothetically if I had a scenario like this. Initial lodge capacity 20 people maximum.
1st booking: 4 people. Check in 8 April 2011 Check Out 12 April 2011.
2nd booking: 2 people. Check in 9 APril 2011 Check out 11 April 2011.
So if I queried the Datefield for 9 April before I made the 2nd booking, it should inform me that there is an existing group present for that day and the remaining capacity is 16. Is that right?
Additionally, how will the database know to "release" the number of people and add it back to the capacity balance once the check out date is exceeded?
I hope what I've written here makes some sense and I apologise if it doesn't. But at least now I have a direction to look at. I look forward to hearing from you all on this matter. Thanks very much!
"The field Facilities::Datefield in your solution sounds like a search field type Global. "
It can be global but doesn't need to be. If you didn't use a global field and created a series of records for a series of dates for the same facility, you could see the available occupancy at a glance for an entire week or other time period. It's all up to what you want to do here.
"So if I queried the Datefield for 9 April before I made the 2nd booking, it should inform me that there is an existing group present for that day and the remaining capacity is 16. Is that right?"
How will the database know to "release" the number of people and add it back to the capacity balance once the check out date is exceeded?
Simply deleting the record that exceedds capacity or changing its date fields to fall on a different date range will automatically update the balance to reflect that change.
For new bookings where you haven't yet checked occupancy, you might hold the booking data in global fields and generate a new record for this data in the table only after you have confirmed availability. Then there is nothing to "roll back".