If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Year over Year Query
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 he 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200711/1 |
#2
|
|||
|
|||
Year over Year Query
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" u20878@uwe wrote in message news:7acb781ce53f0@uwe... 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 he 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200711/1 |
#3
|
|||
|
|||
Year over Year Query
Thanks John. I always forget that I can join to myself. Much appreciated.
Mike John Spencer wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200711/1 |
Thread Tools | |
Display Modes | |
|
|