If you are trying to calculate the percentage of change of the values, then you will need to go back to the original values in order to calculate this, trying to calculate this from a calculated average can give some misleading conclusions unless you actually want to measure the change of the average. If this is the case then this can't be done in a simple single step process as you're calculation will be based on comparing records as opposed to comparing values in different fields (which can be done easily).
I'd suggest making a temp table that will have, based on your example above, the following fields,
a PK field that links the 2 records above,
year2005value,
year2009value,
percChange
Step 1,
use an append query to add records putting values into the PK field and the year2005value with the 2005 values, criteria where year = 2005
Step 2,
use an update query to update the year2009value with the 2009 values, criteria where year = 2009, inner join from the temp table to the original source ON PK field
Once you have these values you can run
Step 3, update the percChange field = (year2009value - year2005value)/year2005value
this will calculate the percentage change giving as a minus figure if the 2009 figure is less than the 2005 figure
This whole approach could also be done in VBA using recordsets, but the above would be the visual approach
David
