Fix the Expr1 Aggregate Function Error in an Access Crosstab Query
Question details
The user needs to resolve an aggregate function error in a Microsoft Access crosstab query.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating a crosstab query with row headings, column headings, and a value field.
- Observed behavior
- Access reports that Expr1 is not part of an aggregate function because the TRANSFORM expression uses the ID field without an aggregate.
Open the crosstab query in Microsoft Access and identify the field used as its value. Decide whether you need to count records, sum numeric values, or return another aggregate for each row and column combination.
Add an aggregate function to the TRANSFORM expression
Use SQL View to replace the unaggregated value expression with a function such as Count.
In the Navigation Pane, right-click the crosstab query, select Design View, and then choose View > SQL View.
Find TRANSFORM ([ID]) AS Expr1 and replace it with TRANSFORM Count([ID]) AS CountOfID. This counts non-null ID values in each crosstab cell.
Keep the row heading in SELECT and GROUP BY, and the column heading in PIVOT. For the fields in this example, the query is: TRANSFORM Count([ID]) AS CountOfID SELECT Main.[Contact].[Value] FROM Main GROUP BY Main.[Contact].[Value] PIVOT Main.[Title];
Select Run on the query design ribbon and check that the resulting counts match the intended row and column groups.

Set the value field's aggregate in Query Design
Use the design grid if you prefer to configure the crosstab without editing SQL.
Work with Crosstab Results in WPS Spreadsheets
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work. Its familiar interface and Microsoft Office format compatibility make it easy to move exported query results into a spreadsheet. It does not fix Access queries or replace Access as a database tool.

Frequently Asked Questions
Why does Access say Expr1 must use an aggregate function?
A crosstab query's TRANSFORM clause must aggregate its value field. An expression such as TRANSFORM ([ID]) AS Expr1 uses ID without an aggregate, so replace it with a suitable expression such as TRANSFORM Count([ID]) AS CountOfID.
Should I use Count or First for the ID field?
Use Count([ID]) to count non-null IDs in each crosstab cell. Use First([ID]) only when returning a single first value is appropriate; it does not guarantee a particular record without an established order.
Where can I change the crosstab aggregate without writing SQL?
Open the query in Design View. In the value field's column, set the Crosstab row to Value and choose an aggregate such as Count in the Total row.
Can WPS Office fix the Access crosstab query?
No. Edit and run the query in Microsoft Access. You can then export the results to an Excel-compatible file and open them in WPS Spreadsheets.




