I am designing a database for our golf holiday company to store booking and customer data as well as create new quotes.

I'm looking for the smartest way to store hotel prices to accommodate both easy updating and the ability to retrieve room price data for a date range and calculate a total price for the length of stay.

So if my Hotel_Prices table contains the following, once the data has been entered into a form can I run a query that will calculate and return Cost and Sell totals for a date range (Check_In to Check_Out) if the date range covers two rows in the price table (Low and High) ?

Hotel_Name
Season (High,Low,Peak,Xmas)
Season_Start (Date)
Season_End (Date)
Room_Type
Cost_Single
Cost_Dbl
Sell_Single
Sell_Double

The obvious (and long winded option) is to store the prices individually for every single day of the year. The Quote query would then simply select the range for the arrive date to depart date and add each day's price together. This obviously means creating a massive amount of table data across all our hotels and room types which I'm hoping to avoid.

Thanks in advance for any assistance.

SB.
Phuket Golf Holidays

All you have to do is to be meticulous in your start and end dates for each category. In other words, the dates covering the four seasons you mention had better cover 365 (or 366) days exactly for each type of room, each amount of occupants, etc.: no overlaps; no gaps.

If you cover all these bases correctly, all you have to do in a query, or in VBA, is use the >, <, =, >=, and/or <= mathematical symbols on the various dates to find the total invoice amount.

Good luck. It really is tedious, but very rewarding when it works like a charm.

SL

different subject but same text as this thread.