Calculating Percentage Diffference

[Cheri]

I am a BRAND new Access user. I am creating a sales report for our commercial real estate business that adds up each tenant's 12 month sales (not necessarily Jan-Dec) and sums it by year. The report that I have created groups sales by tenant, then year. For instance, the report calculates annual sales totals for 2000, 2001, 2002 for Tenant A before totals for Tenant B begin. My problem is this: how do I reference those subtotals within the report so it will calculate the difference between successive years for same tenant??? My ultimate goal is to do calculation for percentage change, year to year, per tenant. Any ideas would be tremendously appreciated.

 

[Jeff Boyce]

Cheri

What you described sounds a lot like what Excel does quite easily. Is there
a reason you don't do the analysis/reporting in Excel?

 

[Jeff Boyce]

Cheri

Thanks. I understand better now.

If your report is already using "bands" to group by Tenant and Year, you
might be able to add unbound controls into the headers or footers of those
groups, and do your percentage calculations there.

I'd probably approach this from the standpoint of queries, though. I'd work
on creating a query (or a chain of queries) that calculated yearly totals,
then further queries that calculated percentage change. I'll point out,
however, that there could be a slight risk that a given Tenant didn't have a
given Year's data, leading to a question of how to handle the calculations
for discontinuous years. If you are 100% certain that this situation does
not exist in your data, ignore the issue. If you are only 100% confident,
better re-check!

 

[Cheri]

Thanks, Jeff. I appreciate your suggestions.

Cheri