Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Feb 17, 2022

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'...
access
  • George_Hepworth's avatar
    George_Hepworth
    Feb 17, 2022

    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.

George_Hepworth's avatar
George_Hepworth
Silver Contributor

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

Tony2021's avatar
Tony2021
Steel Contributor
Feb 17, 2022

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.  

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Feb 17, 2022

    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?

     

     

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Feb 17, 2022

      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.

      • Tony2021's avatar
        Tony2021
        Steel Contributor
        Feb 18, 2022
        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!

Resources