Calculate Year over Year Change

[Matga]

I have a database of 36 months for 50 hotels with many columns of operational data. I want to calculate percent change in the Data1 column from 2002 to 2003 and from 2003 to 2004 for every month and put it in a column on the same query. A complication is that every month I get a new textfile with the latest month of data and losing the oldest month of data so maintaining 36 months of data. In other words, next month I will need to calculate percent change from January 2004 to January 2005 as well.

Can you please help?

Thanks!

Matga

Key Hotel Year Month Data1 Change
3142200201 314 2002 01 4216
3142200202 314 2002 02 3808
3142200203 314 2002 03 4216
3142200204 314 2002 04 4080
etc
3142200201 314 2003 01 5555
3142200202 314 2003 02 6666
3142200203 314 2003 03 8888
3142200204 314 2003 04 7777
etc
3142200201 314 2004 01 9999
3142200202 314 2004 02 8887
3142200203 314 2004 03 7878
3142200204 314 2004 04 8786
etc
etc many hotels with 36 months of data

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Matga]

PH,

I was able to calculate the change using multiple crosstab queries that create a separate column for each year. However, this is very inefficient as it requires a separate query for each data column, and I have many data columns. I would like to do it all in one query. I don't have a clue about how to do this without crosstab queries so have not tried anything.

Thanks!

 

[MrBillSC]

Have you tried using using a parameter query?
MrBill

 

[PHV]

Take a look at self join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Matga]

PHV,

I took a look at self join and got it to work.

Thanks for pointing me in the right direction!