Query Help - calculate variance previous year

[brtucker]

Any help on this query would be appreciates. The data looks like this

ID	P1	P2	P3	YEAR
100100	500	100	100	2012
100100	300	200	300	2011
999999	300	200	300	2012
999999	500	100	100	2011

I would like to calculate the variance between the periods for each ID. ie. P2 in 2012 - P2 in 2011, etc. The final table should look like this...

ID	P1	P2	P3
100100	200	-100	-200
999999	-200	100	200

thanks,
Brent

[June7]

Your data is not normalized structure.

Concerned with only 2011 and 2012?

Consider:

Query1
SELECT Table1.ID, Sum(IIf([Year]=2012,[P1],Null)) AS 2012P1, Sum(IIf([Year]=2012,[P2],Null)) AS 2012P2, Sum(IIf([Year]=2012,[P3],Null)) AS 2012P3, Sum(IIf([Year]=2011,[P1],Null)) AS 2011P1, Sum(IIf([Year]=2011,[P2],Null)) AS 2011P2, Sum(IIf([Year]=2012,[P3],Null)) AS 2011P3
FROM Table1
GROUP BY Table1.ID;

Query2
use the above query as data source and create fields with expressions that calculate difference of respective fields

Have to rebuild queries each year.