Hi,

I am designing a database for a hotel for recording facilities usage. I have two tables in my database, as follows:

Table1: Facilities Charge
Field: Facility No
Field: Facility Type
Field: Charge per Hour
Field: Charge per Night

Table2: Facilities Usage
Field: Facility No
Field: Order No
Field: Room No
Field: Starting Time
Field: Finish Time
Field: Starting Date
Field: Finish Date

There are four types of facilities, renting bicycles, rowing boats, sailing boats, and security car park. The first 3 are calculated on an hourly basis and the last one is calculated on a daily basis (that's why I have seperate fields, is this bad?).

I have created a query based on "Facilities Usage" to insert columns calculating the total time used and most of all, the total charge for a particular order. I am not sure how to use a "match" function like in Excel to match the facility no entered to the relevant charge. Can anyone please help? I'll be deeply grateful.

Sunny

1. Change all your column and table names to eliminate the embedded spaces. Even though Access allows this, it is considered poor practice and VBA does NOT allow it. So if you get to the point of needing macros or code, you'll find the spaces annoying at best.
2. Don't use separate fields for date and time. The Date/Time data type supports storing both and you will find using a single field for calculations to be easier than using two. Look up DateDiff() to see how to find the difference between two date/time fields.
3. Add the unitprice field to the usage table. In order to do historical reporting, you'll need to be able to support price changes. So store the price when the facility is booked. That way changes to the charge table won't affect old rentals.
4. Use a combobox to lookup the facility number and price. In the AfterUpdate event of the facility number field, store the correct price in the usage price field.

Take a look at northwinds.mdb to see similar processing.

Thanks a lot, Pat, for your recommendation. I'll have a work on it! :D