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.

1 Answer 1


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.