Forum Discussion
Sum by Year in addition to by Month
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.
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?
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.
- 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!
- Microsoft Access is like that sometimes. 🙂
