Forum Discussion
Sum by Year in addition to by Month
Hello,
I have developed the following query that sums by month.
I want to add a sum by year too.
How to best do this?
thank you in advance...
SELECT Format([WithdrawalDate],'mmmm yyyy') AS [Month], Sum(tblDraws_CAWC.LocalAcctNoLR) AS SumOfLocalAcctNoLR, Sum(tblDraws_CAWC.LiquidityRes) AS SumOfLiquidityRes, Sum(tblDraws_CAWC.TLocalAcctCalc) AS SumOfTLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'mmmm yyyy');
Maybe it needs to be:
SELECT "Monthly" AS [Date Period], Format([WithdrawalDate],'mmmm yyyy') AS [TimePeriod], Sum(tblDraws_CAWC.LocalAcctNoLR) AS SumOfLocalAcctNoLR, Sum(tblDraws_CAWC.LiquidityRes) AS SumOfLiquidityRes, Sum(tblDraws_CAWC.TLocalAcctCalc) AS SumOfTLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'mmmm yyyy')UNION
SELECT "Yearly" AS [Date Period], Format([WithdrawalDate],'yyyy') AS [TimePeriod], Sum(tblDraws_CAWC.LocalAcctNoLR) AS SumOfLocalAcctNoLR, Sum(tblDraws_CAWC.LiquidityRes) AS SumOfLiquidityRes, Sum(tblDraws_CAWC.TLocalAcctCalc) AS SumOfTLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'yyyy');You'll also want to add an Order by clause to sort the records as needed.
You can use a UNION query:
SELECT "Monthly" AS [Date Period], Format([WithdrawalDate],'mmmm yyyy') AS [Month], Sum(tblDraws_CAWC.LocalAcctNoLR) AS SumOfLocalAcctNoLR, Sum(tblDraws_CAWC.LiquidityRes) AS SumOfLiquidityRes, Sum(tblDraws_CAWC.TLocalAcctCalc) AS SumOfTLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'mmmm yyyy')UNION
SELECT "Yearly" AS [Date Period], Format([WithdrawalDate],'yyyy') AS [Year], Sum(tblDraws_CAWC.LocalAcctNoLR) AS SumOfLocalAcctNoLR, Sum(tblDraws_CAWC.LiquidityRes) AS SumOfLiquidityRes, Sum(tblDraws_CAWC.TLocalAcctCalc) AS SumOfTLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'yyyy');Nice.
I do get a msg box:but I dont even see qryFacDrawsCAWC in the record source?
Could a sort on the months be added? I think that since its a Union I lose the query design window.
Correct Union queries can only be edited in SQL view.
At first glance I'm not sure why the parameter request is being displayed.
Did you write your SQL exactly the same way I illustrated it? And is qryFacDrawsCAWC the name of the Union query itself?
