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

Question

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.

Answer 1

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)