Year over Year Comparision of Portfolio

[richard70]

Hello. I would like to compare year over year total property revenue by month. Each monthly total shows the grand total of all valid properties. A property can only be included in a monthly comparison with the previous year if that property was active durring the previous year and current year for that month. For example, a property obtained in Feb 2010 would be exclded from contributing to the total revenue in Jan 2011, because it was not active in Jan 2010. I need to compare apples with apples. I have a revenue table showing the hotel ID, the month and year of the stay, and the revenue amount. I also have a property table that shows when a property became active, and when it terminates. Another issues is that not every property has revenue for each month. So if a property has no revenue in Feb 2010, it still needs to contribute to total revenue of Feb 2011, provided that it was active in Feb for both years. The tricky part is that revenue may be missing for a property because it has no revenue for that month, Or because I am trying to filter that property out of that month because I can't compare it with the next year. I don't know how to determine if data missing in one year means that I also need to exclude that property from the comparison year. The only time it should be excluded from a monthly grand total is if that property was not active durring both years for that month. Please help if you know what needs to be done. Thanks.

[June7]

I expect will require multiple queries to accomplish. Maybe a query for each year, grouping by hotel and month, use an alias field name for the revenue field that includes the year. Join the queries on hotel and month fields, jointype of only where fields in both queries are equal. This should give side-by-side revenue columns for each year.

[richard70]

Thank you June7. That is how it was done. I appreciate your advice!