Calculating YTD performance information (sales, occupancy) in a query

[chapmasco]

I am trying to calculate year to date sales and occupancy (htoel company) performance in a an access query. The query actually feeds to an access application.

I am feeding off some ODCB tables on the network and I recevie the following information: Room in hotel; monthly occupancy (rooms sold/rooms available);monthly revenue per available room (total revenue/rooms available).

The year and month are separate database cells. What I would like to do is calculate the year to date occupancy and revenue. I have the monthly informaiton.

#1 do I need to write separate queries for each year?
#2 I would like the YTD occupancy to appear in the same column for all years. When writing the formulas, can I indicate what years and months to choose for the calculation? For instance to calculate occupancy for 2008 YTD, how would I write the formula in Access to calculate YTD occupancy for March 2008. How do I distinguish the same formula to show September 2008?

I'm pretty much self taught in Access so laymens terms please. Thanks

 

[MSAccessRookie]

I am trying to calculate year to date sales and occupancy (htoel company) performance in a an access query. The query actually feeds to an access application.

I am feeding off some ODCB tables on the network and I recevie the following information: Room in hotel; monthly occupancy (rooms sold/rooms available);monthly revenue per available room (total revenue/rooms available).

The year and month are separate database cells. What I would like to do is calculate the year to date occupancy and revenue. I have the monthly informaiton.

#1 do I need to write separate queries for each year?
#2 I would like the YTD occupancy to appear in the same column for all years. When writing the formulas, can I indicate what years and months to choose for the calculation? For instance to calculate occupancy for 2008 YTD, how would I write the formula in Access to calculate YTD occupancy for March 2008. How do I distinguish the same formula to show September 2008?

I'm pretty much self taught in Access so laymens terms please. Thanks

• Do I need to write separate queries for each year?
  • You should not need to have a separate query for each year (or each Month for that matter). Using a sum() of the occupancy along with Group By statements for the Years (or Years and Months) should get you what you want.
• I would like the YTD occupancy to appear in the same column for all years.
  • That depends on your Query. The YTD Occupancy (Sum of Occupancy) should appear in whatever column you select it in.
• When writing the formulas, can I indicate what years and months to choose for the calculation?
  • If you a parameter for the Month And/Or the Year, you can use IIf to change the dates in the Between Range.