Year over Year Query

[michaelloveusa via AccessMonster.com]

Hi there.

I haven't worked on Access for a few years and I was asked today to help
someone with a query to calculate Year over Year percentage changes. Here is
the current situation:

A single table has these columns (simplified) and sample data:

Year TaxAmount
2006 55000
2005 50000
2004 43000

What I need to do is dynamically create another column that calculates the
YOY change for each year. The formula is simple (CurrentYear TaxAmount -
Previous Year Tax Amount) / Previous Year Tax Amount. Example shown here:

Year TaxAmount YOYPercent
2006 55000 10.0%
2005 50000 25.0%
2004 40000 null

Easy to calculate, but not sure how to dynamically use current year to get
previous year's amount. Should I try a subselect or a function etc? Been a
while since I have done Access, so any help would be appreciated.

Thanks, Mike

 

[John Spencer]

One method (and perhaps most efficient) would be to join the table to itself
using a non-equi join .

Assumption: Year is a number field. If not, you will need to convert the
year to a number using one of the conversion factors.

SELECT A.Year
, B.Year
, A.TaxAmount-B.TaxAmount/ B.TaxAmount as YOY
FROM YourTable As A LEFT JOIN YourTable as B
ON A.Year = B.Year+1

BY the way, I know you said this was simplified, but you are aware that YEAR
is a reserved word since it is a function to return the Year number from a
date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[michaelloveusa via AccessMonster.com]

Thanks John. I always forget that I can join to myself. Much appreciated.

Mike

One method (and perhaps most efficient) would be to join the table to itself
using a non-equi join .

Assumption: Year is a number field. If not, you will need to convert the
year to a number using one of the conversion factors.

SELECT A.Year
, B.Year
, A.TaxAmount-B.TaxAmount/ B.TaxAmount as YOY
FROM YourTable As A LEFT JOIN YourTable as B
ON A.Year = B.Year+1

BY the way, I know you said this was simplified, but you are aware that YEAR
is a reserved word since it is a function to return the Year number from a
date.

Hi there.

[quoted text clipped - 24 lines]

Thanks, Mike