How to calculate differences

[ghodgkins]

I have about six categories that I rate various items against. I created a summary report and query that allows me to get the average of each category for the past two years. I was wondering if there's a way to get a calculation that would show the difference in averages, year over year?

This is what I'm hoping to see:

	Avg of A	Avg of B	Avg of C	Avg of D	Avg of E	Avg of F
2014	1.75	2.00	1.50	3.00	2.75	2.50
2015	2.00	1.75	1.50	2.25	2.25	3.00
	0.25	-0.25	0.00	-0.75	0.50	0.50

[ranman256]

You could make a query off this query (above)
Your query would need pull data to the same record to get: (2014Avg), (2015Avg), (2014Avg)- (2015Avg) as Diff

[ghodgkins]

Sorry... Not quite sure what you mean...

[June7]

That requires getting value from another record in same table. One way is with nested subquery. Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

[ranman256]

make query Q2014 that avg only 2014
make query Q2013 that avs only 2013
then Q3 =
SELECT [2013].ClientID, [2014].Avg2014, [2013].Avg2013, [Avg2013]-[Avg2014] AS Diff
FROM 2013 INNER JOIN 2014 ON [2013].ClientID = [2014].ClientID;

[June7]

And another method as suggested by ranman. However, it assumes the same clients will be in both years. If this is not the case, then join both queries to a master dataset of clients.