Create an Aggregate Query Like a PivotTable in Microsoft Access
Question details
Create reusable Access query results that sum quantities for each transaction and product.

- 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.
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.
Group transaction and product quantities in an Access query
Use a GROUP BY query to produce one reusable row per transaction and product.
In Access, select Create > Query Design, close the table-selection window if it appears, and switch to SQL View.
Enter: SELECT [Transaction], [Product], SUM([Quantity]) AS TotalQuantity FROM [NameOfTableGoesHere] GROUP BY [Transaction], [Product]; Replace the table and field names with your own.
Select Run to check the totals, then save the query with a name such as qryTransactionProductTotals.
Create another query and add qryTransactionProductTotals as its source, or refer to the saved query by name in SQL View.

Display product totals as columns with a crosstab query
Use a crosstab when you want a PivotTable-like layout with transactions as rows and products as columns.
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.

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.




