How to Use an Access UNION Subquery to Find the Maximum Value by Group
Question details
The user needs to combine three Microsoft Access tables that contain group identifiers and values, and then find the maximum value for each group using a subquery instead of a separate UNION query.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Querying and aggregating grouped data across multiple tables with identical structures within a relational database.
- Observed behavior
- The user wants to streamline their database operations by dynamically retrieving the maximum values for each group via a single SQL query structure, avoiding the creation of intermediate saved UNION queries.
Ensure you have the exact table names and the precise field names for your group identifiers (e.g., fieldG) and the values you want to maximize (e.g., fieldV) before writing your SQL syntax.
Use a UNION Query as a Derived Table
Combine the multiple tables directly within the FROM clause and apply the MAX function to the grouped results.
This method allows you to merge multiple tables in a single step using a derived table, which is a subquery located in the FROM clause. It completely avoids the need to save the UNION query as a separate query object in your Access database, keeping your workspace clean.
Open your Microsoft Access database, navigate to the Create tab on the top ribbon, and click on Query Design.
Close the Show Table dialog that appears, then right-click the query tab or click the View button in the top-left corner and select SQL View.
Type the SQL statement to combine the tables and group them: SELECT fieldG, Max(fieldV) AS MaxOfFieldV FROM (SELECT * FROM Tbl1 UNION SELECT * FROM Tbl2 UNION SELECT * FROM Tbl3) AS T GROUP BY fieldG;
Click the Run button (red exclamation mark) on the Query Design tab to execute the SQL. You will see the maximum values for each group.
Use a Correlated Subquery for Merged Data
If your data is already combined into a single unified table, use a correlated subquery in the WHERE clause.
Switch to WPS Office for Powerful Data Management
While MS Access handles complex relational databases, WPS Spreadsheet offers an excellent, lightweight alternative for everyday data analysis, sorting, and cross-sheet aggregations without needing advanced SQL knowledge.
- 1. Download and Install: Download WPS Office from the official website and install it on your preferred device.
- 2. Import Your Data: Export your Access data as a CSV or XLSX file and open it seamlessly within WPS Spreadsheet.
- 3. Aggregate by Group: Use the Data tab to create a PivotTable, dragging your group identifier to Rows and your value to Values, setting it to summarize by Max.
Frequently Asked Questions
Can I use UNION ALL instead of UNION in the subquery?
Yes. UNION ALL is faster because it does not expend processing power checking for duplicate rows. If you know the datasets across the three tables are distinct or if duplicates do not affect your MAX calculation, you should replace UNION with UNION ALL.
Why does Microsoft Access require an alias for the derived table?
MS Access SQL requires that any subquery used in the FROM clause (known as a derived table) be given an alias, such as 'AS T'. This provides a temporary name so the main outer query can reference its columns properly.
Can I find the minimum value using this exact same approach?
Absolutely. You can simply replace the Max() function with the Min() function in your outer SELECT clause to find the lowest value for each group across the combined tables.
What is the advantage of using a correlated subquery over a standard GROUP BY query?
A correlated subquery allows you to easily pull additional columns (like the date or specific transaction ID) from the exact same row where the maximum value occurred. Achieving this with a standard GROUP BY query often requires additional joins back to the original table.
