Скачать или смотреть How to Sum Matching Criteria from Two Columns in MS Access Tables

Learn how to effectively sum matching criteria from two columns in your MS Access database. This guide walks you through creating an SQL query to display total sales for each month and year.
---
This video is based on the question https://stackoverflow.com/q/72395260/ asked by the user 'JDDellGuy' ( https://stackoverflow.com/u/5106564/ ) and on the answer https://stackoverflow.com/a/72395883/ provided by the user 'EMF' ( https://stackoverflow.com/u/4842667/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MS Access - How to sum matching criteria from two columns in same table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Sum Matching Criteria from Two Columns in MS Access Tables

Handling data in Microsoft Access can be challenging, especially when you want to aggregate information based on specific conditions. A common scenario for database users is the need to sum values from different columns that correlate with certain criteria. In this guide, we’ll take a closer look at how to sum matching criteria from two columns in the same table, specifically focusing on sales data.

The Problem at Hand

Imagine you have a database table for job sales that records original sale amounts and their respective dates, along with revision amounts and dates. You want to create a report that clearly separates the data by month and year, summing up both the original and revised sales for that period. Here's a quick outline of what you're dealing with:

Columns of Interest:

OriginalSaleAmount: The initial sales figure.

OriginalSaleDate: The date of this original sale.

RevisionSaleAmount: Any changes made to the sales figure.

RevisionSaleDate: The date the revisions occurred.

Example Data Structure

The data in your table might look something like this:

JobNoOrigSaleAmntOrigSaleDtRevSaleAmntRevSaleDt12345$9876501/05/2022$50601/29/202212346$1234501/24/2022$102802/27/202212347$1357902/07/2022$94303/12/202212348$1235803/16/2022$72903/19/202212349$1793503/29/2022$682104/25/2022From this data, your goal is to create a new summary table that looks like this:

DateYearDateMonthTotalSalesComment2022January$111616$98765 + $12345 + $5062022February$14607$13579 + $10282022March$31965$12358 + $17935 + $943 + $7292022April$6821$6821The Solution

To achieve your goal, you will need to use SQL in MS Access. Follow the steps below to write a proper SQL query that can sum your sales figures based on the criteria mentioned.

Step-by-Step Guide

Open Query Designer: Start MS Access and navigate to the Query Designer. Here you should see an option marked View in the top left corner. Click on it and choose SQL. This will open a blank SQL query window.

Input the SQL Query: Replace any existing text with the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Important Note: Make sure to replace Sales with your actual table name if it's different.

Understanding the Query:

The inner query retrieves and combines both original and revised sales data using UNION ALL, which effectively appends the two result sets.

The outer query then groups the results by DateYear and DateMonthNum, calculating the total sales using the SUM function.

Executing the Query

After typing in your SQL query, run it to see your results. You should now have a neatly organized summary of total sales by month and year, making it much easier to interpret the data.

Conclusion

By following the above steps, you can efficiently sum matching criteria from two columns in your MS Access database. This approach not only saves time but also ensures accuracy in your reporting. Whether you're transitioning data from spreadsheets or building a new database, mastering SQL queries in Access is a valuable skill that enhances your data management capabilities. Happy querying!