Listing Values Based on Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table storing GeneralRoomRates for different types of hotel rooms identified by unique RoomID, and another table which keeps SpecialRoomRates for different periods with the following data fields : RoomID, PeriodStart (a calendar date), PeriodEnd (a calendar date), SpecialRate. For example, for RoomID=Deluxe, GeneralRoomRate=$150, PeriodStart(1)=Feb 1, PeriodEnd(1)=Feb 18, SpecialRoomRate(1)=$130 <this is a promotional period>, PeriodStart(2)=July 1, PeriodEnd(2)=July, 31, SpecialRoomRate(2)=$170 <this is a high period>. There will be similar records for different RoomID, with different PeriodStart / PeriodEnd / SpecialRoomRate.
I would like a query to be able to list the varying room rates for each RoomID in a chronological order : e.g. for RoomID=Deluxe, $150 for Jan 1 - Jan 31, $130 for Feb 1 - Feb 18, $150 for Feb 19 - Jun 30, $170 for July 1 - July 31, $150 for Aug 1 - Dec 31. If I could produce a query with different RoomID on top and each of the 365 days at the left, then I could generate another query to list the different room rates for specific check-in / check-out dates.
I have checked all the Date functions but couldn't figure out how to do the first query. Any assistance will be appreciated.
 
I would like a query to be able to list the varying room rates for each RoomID in a chronological order : e.g. for RoomID=Deluxe, $150 for Jan 1 - Jan 31, $130 for Feb 1 - Feb 18, $150 for Feb 19 - Jun 30, $170 for July 1 - July 31, $150 for Aug 1 - Dec 31. If I could produce a query with different RoomID on top and each of the 365 days at the left, then I could generate another query to list the different room rates for specific check-in / check-out dates.
Click to expand...

You'll need an auxiliary 365 (or 366, in leap years) row table with a
single datefield and one row for each day (you can fill this table
easily by using Excel to fill-down a date field and importing the
spreadsheet). It might be easiest to include the appropriate rate on
each row (albeit redundant).

Create a Query with this table and your rooms table and use a Crosstab
query and you should get the grid you want.
 
Back
Top