Others

Create an Aggregate Query Like a PivotTable in Microsoft Access

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

Create reusable Access query results that sum quantities for each transaction and product.

Create an Aggregate Query Like a PivotTable in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
A transaction can contain multiple products, and the user wants a PivotTable-like summary that can be used in further queries.
Observed behavior
The desired result is a total quantity for each transaction and product combination.
Before you start

Identify the table and fields that hold the transaction, product, and quantity values. Replace the example table and field names with the names in your database, and confirm that Quantity is numeric.

Solution 1Recommended

Group transaction and product quantities in an Access query

Use a GROUP BY query to produce one reusable row per transaction and product.

1
Open SQL View

In Access, select Create > Query Design, close the table-selection window if it appears, and switch to SQL View.

2
Enter the aggregate query

Enter: SELECT [Transaction], [Product], SUM([Quantity]) AS TotalQuantity FROM [NameOfTableGoesHere] GROUP BY [Transaction], [Product]; Replace the table and field names with your own.

3
Run and save the query

Select Run to check the totals, then save the query with a name such as qryTransactionProductTotals.

4
Reuse the results

Create another query and add qryTransactionProductTotals as its source, or refer to the saved query by name in SQL View.

Group transaction and product quantities in an Access query
Grouping level: Each row represents a transaction and product combination. A transaction with multiple products therefore appears in multiple rows.
Free Microsoft Office alternative

Summarize Transaction Data in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface and compatibility with Excel files, making it easy to move spreadsheet data and build PivotTables. It does not run Access queries or replace an Access database.

Summarize transaction quantities by product with spreadsheet PivotTablesOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for an easier transitionWork with a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to group by both transaction and product?

Grouping by both fields creates a separate total for each transaction-product combination. In SQL View, include both fields in the SELECT and GROUP BY clauses.

How can I use the aggregate results in another query?

Save the aggregate query, then select it as a source in a new query through Create > Query Design. You can also reference the saved query name in SQL View.

What is the difference between GROUP BY and a crosstab query?

GROUP BY returns a row for each transaction and product combination. A crosstab uses PIVOT to display product values as separate columns.

Why might a crosstab query cause problems in a later query?

Its product columns can change when product values change. If a later query needs predictable columns, consider specifying fixed crosstab column headings or using the GROUP BY result instead.