is this data summarized or is this a view of your actual data? If it's your actual data (it might work if this is summarized too, but I'm not sure).
Let's assume this is your full dataset and it's on a table named 'TABLE1'
This query would give you the most recent quarter prior to each of your records:
Code:
SELECT Table1.Area, Table1.YYZ, Table1.Quarter_Date AS ThisQuarter, Max(Table1_1.Quarter_Date) AS LastQuarter
FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.Area = Table1_1.Area
WHERE (((Table1_1.Quarter_Date)<[table1]![Quarter_Date]))
GROUP BY Table1.Area, Table1.YYZ, Table1.Quarter_Date;
Save this query as QUERY1
then create this query
Code:
SELECT Query1.Area, Query1.YYZ, Query1.ThisQuarter, Table1.YYZ AS ThisQ, Table1_1.YYZ AS LastQ, [table1]![yyz]-[table1_1]![yyz] AS NetChange
FROM (Query1 LEFT JOIN Table1 ON (Query1.ThisQuarter = Table1.Quarter_Date) AND (Query1.Area = Table1.Area)) LEFT JOIN Table1 AS Table1_1 ON (Query1.LastQuarter = Table1_1.Quarter_Date) AND (Query1.Area = Table1_1.Area)
ORDER BY Query1.Area, Query1.ThisQuarter;
This will link the quarter dates back to the original table, pull the correct yyz values and give you the net change