MS Access - How to sum matching criteria from two columns in same table



  • I have an MS Access database with four columns of interest that I am trying to work with.

    The rows represent sold jobs, and the columns of interest are "OriginalSaleAmount", "OriginalSaleDate", "RevisionSaleAmount", and "RevisionSaleDate." You can see that there is a relationship between the date and sale columns, but of course Access is not inherently aware of this.

    I would like to have either a report, or a view of some kind, which has a row for each month of the year, and which shows the combined sum of "OriginalSalesAmount" and "RevisionSaleAmount" which pertains to that month of the year. Of course, the years themselves should be distinguished also. We don't want data for January 2021 and January 2022 to be summed together; each should be its own year.

    Here is an example to help clarify:

    Let's say I have this table.

    JobNo OrigSaleAmnt OrigSaleDt RevSaleAmnt RevSaleDt
    12345 $98765 01/05/2022 $506 01/29/2022
    12346 $12345 01/24/2022 $1028 02/27/2022
    12347 $13579 02/07/2022 $943 03/12/2022
    12348 $12358 03/16/2022 $729 03/19/2022
    12349 $17935 03/29/2022 $6821 04/25/2022

    I need a table, query, or report that produces this information from that table.

    DateYear DateMonth TotalSales Comment
    2022 January $111616 $98765 + $12345 + $506
    2022 February $14607 $13579 + $1028
    2022 March $31965 $12358 + $17935 + $943 + $729
    2022 April $6821 $6821

    You'll notice that each month sums the sale amount that corresponds to the date that is associated with a particular sales column.

    I'm not a strong Access user and I'm not entirely sure where to start with this sort of data handling. If anyone can help guide me in how to go about accomplishing this sort of thing, I'd appreciate the help.



  • Just a sketch, no idea whether it works in access:

    SELECT year(dt), month(dt), sum(amnt)
    FROM (
        SELECT OrigSaleDt as dt, OrigSaleAmmnt as amnt FROM T
        UNION ALL
        SELECT RevSaleDt as dt, RevSaleAmmnt as amnt FROM T
    ) AS X
    GROUP BY year(dt), month(dt) 
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2