Year over Year Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

michaelloveusa - 06 Nov 2007 06:21 GMT

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

Reply to this Message

John Spencer - 06 Nov 2007 12:56 GMT

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.

Signature

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

> Hi there.
>
[quoted text clipped - 24 lines]
>
> Thanks, Mike

Reply to this Message

michaelloveusa - 06 Nov 2007 20:30 GMT

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 .
[quoted text clipped - 17 lines]
>>
>> Thanks, Mike

Reply to this Message