Sum by Year in addition to by Month

[Tony2021]

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');

[#2]

@Tony2021 

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');

[#3]

@George Hepworth 

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.  

[#4]

@Tony2021 

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?

[#5 · Accepted solution]

@George Hepworth 

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.

[#6]

I think I had some kind of corruption. I separated out the queries and ran them separately and I didnt get that parameter msg box. I then combined them into the union query and it still gave me the parameter msg box. I had to delete it then re-create it. Not sure if coudl have been that it was a non union query initially. Anyways, its working. thank you very much!

[#7]

Microsoft Access is like that sometimes. :)