Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Question 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) ?

    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
    Last edited by PGH; 08-23-10 at 12:46. Reason: Additional Info

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    duplicate post

    different subject but same text as this thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •