Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Feb 17, 2022
Solved

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
Feb 17, 2022

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
    Iron 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.

Resources