There is probably a more elegant way to do it with sub queries but I think this works:
Crete aa query called qryTotalA with the following SQL:
Code:
TRANSFORM Sum(YourTable.Amount) AS SumOfAmountSELECT YourTable.TransactionMonth
FROM YourTable
WHERE (((YourTable.Section)="a"))
GROUP BY YourTable.TransactionMonth
PIVOT YourTable.Section;
Crete aa query called qryTotalB with the following SQL:
Code:
TRANSFORM Sum(YourTable.Amount) AS SumOfAmountSELECT YourTable.TransactionMonth
FROM YourTable
WHERE (((YourTable.Section)="b"))
GROUP BY YourTable.TransactionMonth
PIVOT YourTable.Section;
Create a query with the following SQL which will give the required results:
Code:
SELECT qryTotalA.TransactionMonth, qryTotalA.a, qryTotalB.b, [a]-[b] AS ResultFROM qryTotalB INNER JOIN qryTotalA ON qryTotalB.TransactionMonth = qryTotalA.TransactionMonth;