Unanswered: Possible to calculate percent change between periods?
I am wondering if it is possible to generate a calculated field that shows the percent change from one period to another for a particular field.
Specifically, I want to show the percent change for each month from the similar month one year ago and have the results match up to the corresponding month. So the percent change from June 2002 to June 2003 would be in a table with the date as June 2003. I am now exporting the data into Excel and performing the calculations there and importing the results back into Access. I would like to do the whole thing within Access if possible.
I have come up with one solution where I create a previous year date field that is based on a function of the current year date. Then run a make table query that generates a second table with the two date fields in it as well as all of the data. Then I run a query that calculates the percent change between the comparable fields in both the new table and the origional tables using the new date field as a join to the original date field.
It seems to work but I wonder if there is a cleaner way to do this. I have well over 100 fields in this table and it is a lot of calculated fields to generate (I also need to do this with five additional datasets all with different field names so that works out to about 600 calculated fields). I could probably create the SQL string using the concatenate function in Excel but I am convinced that there has to be an easier way to do this.
Thanks for your suggestion. Unfortunately it still leaves me with the task of creating over 600 calculated fields.
I figured out a temporary solution. I ran a crosstab on the table using the year and data descriptions( current column headers) as row headers and the month as the column headers. This gives me a table where january data is all in one column, february data is all in one column, etc. Then I created a query that generates a new year field that is equal to the current minus one. I then do a calculation for each month matching the old date to the new date and the data descriptions to get the year to year percent change.
Next I run a series of consecutive append queries to put all the data in one column, all the month names in another, another for the years, and one for the data descriptions. I then recreate the date field and then run a crosstab based on that table.
It works well and I only hade to create twelve calculated fields. I am sure there must be some way to do this by running a loop in VBA which would probably be a lot cleaner and use less resources.
It seems to me it would be easier to generate a spreadsheet to display your results, rather than using a cross-tab query based on an intermediate table.
Would you be interested in seeing something that could take your table and produce such a spreadsheet (months across the top, fields listed down the first column, populated with the %change from the prior year) using no intermediate tables?
The only requirement to use it would be: all boxes that run the app have to have excel installed.
If it is going to be more efficient than the way I have set it up, sure. What I have done in the past is set up an Excel workbook with a page of just references to a workbook that is created using the "output to" feature in access and perform the calculations in that workbook. The problem is that the data needs to be in Access for a third party app that I need to ultimately get the data into. I can always link it back into Access from Excel if need be.
I appreciate your insight - any suggestions would be welcome.
The table is originally SAS output where each row has 14 fields. Code, Year, and the twelve months. Each of the twelve months contains data. It has 20 years worth of data for each code so the year is repeated once for each unique code. There are up to 150 different codes. -- So in this case the row header is actually two fields - code and year and the column headers are the twelve months. --
After some manipulation I have a table with the dates (in regular date format) as the row headers and the codes as the column headers.
So I basically have two possible table formats. I am not sure if that answers your question or not. I think the second format I described would fit what you are asking.
The original data format seems pretty conducive to calculating a year over year percent change and I only have to create a calculation for each month instead of each code (series). Then I just run it through the same manipulation process that I use for the level data (I can even use the same queries - just use a temporary table and rename once I am done).