Hello,

I am stuck trying to figure out how to make a query that takes data that is constantly being appended to and break it out into several date-bound criteria. The database models a hotel reservations desk and is ideally used for reporting on the amount of inventory sold between dates. Here is the current structure with descriptions. Below that is the explanation of what I am trying to get to. Any advice is most appreciated.

tblData : Table holding all available data. For the sake of this explanation, here are the available fields in this table which are updated daily:
[Key] : concatenation of [Data_as_of_Date] and [Date]
[Data_as_of_Date] : date the record was created
[Date] : Date in which the room will be occupied
[RN] : the number of rooms that will be occupied on [Date]
qryCurrentYear : Query that pulls all fields in tblData with the current year as the only parameter
qryCurrentDate: Query that pulls all fields in tblData where [Data_as_of_Date] is equal to today
qryYesterdayDate : Query that pulls all fields in tblData where [Data_as_of_Date] is equal to yesterday
qrySevenDate : Query that pulls all fields in tblData where [Data_as_of_Date] is equal to seven days ago

tblData is updated daily with the number of [RN] for a given [Date] changing constantly. I need to figure out a way to get from where I am to a query/report that lets me show the following for a full calendar year (the | indicates columns):

[Date] | [RN] (current – from qryCurrentDate) | [RN] (yesterday – from qryYesterdayDate) | [RN] (seven days ago – from qrySevenDate)

I cannot figure out how to get here, perhaps I am going about this wrong? Also, once a date has passed I would like to show the data [RN] for whatever the final [RN] count was. In other words, today is 7/2/14, so for 7/1/14, I want to show whatever the [RN] was as of 7/1/14, which could be identified via [Key] (which would be the concatenation of 7/1/14 & 7/1/14).

Thank you very much,

learning_vba