Search code examples
sqlms-access

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

I posted this over in database administrators, but it's not getting much attention there, so I'm trying here.

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 do understand that the more proper way to do this is probably to have a checkbox field which specifies whether a given record is a revision or not; that way the dates and dollar amounts would all be in the same columns and could be easily added up. But I am converting this into Access from a spreadsheet for one of our departments and need it to match the original spreadsheet layout as closely as possible at this time.

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.

Solution

  • Sometimes, you need SQL. :)

    While in the Query Designer, you should see a dropdown button near the top left marked View. Click that and choose SQL. This will give you an SQL query window. Replace whatever is in there with this:

    SELECT DateYear, MonthName(DateMonthNum) as DateMonth, SUM(Amnt) AS TotalSales FROM
(
SELECT Year(OrigSaleDate) AS DateYear, Month(OrigSaleDate) AS DateMonthNum, OrigSaleAmnt as Amnt
FROM Sales
UNION ALL
SELECT Year(RevSaleDate) AS DateYear, Month(RevSaleDate) AS DateMonthNum, RevSaleAmnt as Amnt
FROM Sales
)
GROUP BY DateYear, DateMonthNum
ORDER BY DateYear, DateMonthNum

    This assumes your table is named Sales. If not, replace "FROM Sales" with "FROM [your table name]".

    The inner query selects the original sale info, then appends the revised sale info (UNION ALL). The outer query selects from and aggregates the result of the inner query.