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

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.