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

[JDDellGuy]

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.

Note: I did post this originally over at https://dba.stackexchange.com/questions/312399/ms-access-how-to-sum-matching-criteria-from-two-columns-in-same-table but didn't really get much in the way of a useful answer.

[ranman256]

Q1 would sum your columns for the month : select month([datefld])) ,Year([DateFld]), sum(Sales) from table

Q2 can then sum the fields from Q1 for the yearly view.

[Welshgasman]

Structure all wrong,making it harder to get what you want.
Structure should be more like
Jobno
SaleDate
SaleAmt
SaleType ' sale or rev

Then easy to sum, grouping on Year(saledate)

[Welshgasman]

Possibly use a union query as q1 to get the data in a more normalsed form, then q2 to sum and group it?