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.
I need a table, query, or report that produces this information from that table.
JobNo OrigSaleAmnt OrigSaleDt RevSaleAmnt RevSaleDt 12345 $98765 01/05/2022 $506 01/29/202212346 $12345 01/24/2022 $1028 02/27/202212347 $13579 02/07/2022 $943 03/12/202212348 $12358 03/16/2022 $729 03/19/202212349 $17935 03/29/2022 $6821 04/25/2022
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.