Unanswered: Table design/Date Range Query Advice please
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) ?
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.
Phuket Golf Holidays
Last edited by PGH; 08-23-10 at 12:46.
Reason: Additional Info
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.