Query Confusion -- Please help

I have three categories (cat1, cat2, cat3) that are updated each month at different times.
Cat1 may have multiple entries for the month.
I need monthly totals for each category and a monthly grand total for all categories.
Here is my problem. We have to start with a certain balance and continue from there. The balance for the end of January is the beginning value for February.
SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS SumOfcat3, Sum(Transactions.cat2) AS SumOfcat2, Transactions.monthID, (sumofcat1+sumofcat2+sumofcat3) AS MonthlyTotal
FROM Transactions
GROUP BY Transactions.monthID
ORDER BY Transactions.monthID;
The above query returns the following
SumofCat1 SumofCat2 SumofCat3 MonthID MonthlyTotal
-- -- -- -- --
90 0 50 1 140
58 9 108 2 175
50 100 100 3 250
I need another field called GrandTotal that shows as follows
GrandTotal
140
315
575
Please help. I looked at the http://www.databasejournal.com/featu...le.php/3112381 site with the grand total tutorial, but can't get it to work in access.
hi jason,
Try following query:
select
SumOfcat1, SumOfcat3, SumOfcat2, monthID , (sumofcat1+sumofcat2+sumofcat3)
AS MonthlyTotal ,
(select sum(cat1)+sum(cat2)+sum(cat3)
from transactions a
where a.monthid <= x.monthid ) 'grandtotal'
from
(SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS
SumOfcat3,
Sum(Transactions.cat2) AS SumOfcat2, monthID
FROM Transactions
GROUP BY monthID)X
ORDER BY monthID;
Vishal Parkar
vgparkar@.yahoo.co.in