Others

Fix the Expr1 Aggregate Function Error in an Access Crosstab Query

Steve KSteve K Nov 20, 2026 869 views

Question details

The user needs to resolve an aggregate function error in a Microsoft Access crosstab query.

Fix the Expr1 Aggregate Function Error in an 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.
Before you start

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.

Solution 1Recommended

Add an aggregate function to the TRANSFORM expression

Use SQL View to replace the unaggregated value expression with a function such as Count.

1
Open SQL View

In the Navigation Pane, right-click the crosstab query, select Design View, and then choose View > SQL View.

2
Edit the value expression

Find TRANSFORM ([ID]) AS Expr1 and replace it with TRANSFORM Count([ID]) AS CountOfID. This counts non-null ID values in each crosstab cell.

3
Check the headings

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];

4
Run the query

Select Run on the query design ribbon and check that the resulting counts match the intended row and column groups.

Add an aggregate function to the TRANSFORM expression
Choose the right aggregate: Use Sum for appropriate numeric values, Min or Max for an extreme value, or First when any first value is suitable. First does not guarantee a particular record without an established order.
Free Microsoft Office alternative

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.

Review and analyze exported crosstab results in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesWork with Word DOCX and PowerPoint PPTX filesUse a familiar interface in a lightweight office suite
microsoft office alternative - wps office

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.