Year over Year Query

  • Thread starter michaelloveusa via AccessMonster.com
  • Start date
M

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
 
J

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
..
 
M

michaelloveusa via AccessMonster.com

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

Mike

John said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top