Others

Fix Microsoft Access Aggregate Function Error for the ID Field

Camila MilosovichCamila Milosovich Nov 14, 2026 868 views

Question details

The user needs to correct an Access Totals query that reports ID is not part of an aggregate function.

Fix the Microsoft Access Aggregate Function Error for ID
Product
Microsoft Access
Device & OS
not provided
Scenario
A query groups or aggregates records while selecting the ID field.
Observed behavior
Access reports that the query does not include the specified expression ID as part of an aggregate function.
Before you start

Identify the query that produces the error and decide whether you need one result per ID or a summary across multiple IDs. Have the query's SQL available if the required grouping is unclear.

Solution 1Recommended

Correct the Totals settings in Access Query Design

Use Design View to group, aggregate, or remove selected fields according to the result you need.

1
Open the query

In the Navigation Pane, right-click the affected query and select Design View.

2
Show the Totals row

On the Query Design tab, select Totals (Σ) to display the Total row in the design grid.

3
Set a total for each field

In each column's Total row, select Group By for fields you want to group by, or choose an aggregate such as Sum, Count, or Max when that matches your goal. For example, choose Count for ID if you need a count rather than individual IDs.

4
Remove unneeded fields

Select and delete any query-grid columns that are not needed in the result. Adding ID to Group By can create separate groups for each ID, so remove or aggregate it instead if you need a broader summary.

5
Run the query

Select Run on the Query Design tab and check that the results have the intended grouping.

Correct the Totals settings in Access Query Design
Check calculated expressions: Calculated expressions must also be compatible with the grouping or use aggregate functions as appropriate. The exact correction depends on the query's SQL and intended result.
Free Microsoft Office alternative

Analyze exported Access data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported spreadsheet data. It supports common Microsoft Office formats, making it easy to move compatible files into WPS. It does not repair Access queries or replace Access databases.

Review and summarize data exported from Access in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access say that ID is not part of an aggregate function?

In a grouped query, a selected ID field may not be grouped or aggregated as required. Open the query in Design View and check its Total row, or inspect the SELECT and GROUP BY clauses in SQL View.

Should I set ID to Group By or Count?

Choose Group By if you need a separate result for each ID. Choose Count if you need to count ID values within each group. Grouping by a unique ID can prevent records from being summarized together.

Where is the Totals row in Access?

Open the query in Design View and select Totals (Σ) on the Query Design tab. The Total row appears in the query design grid.

Can WPS Office fix an Access aggregate query?

No. Correct the query in Microsoft Access. WPS Spreadsheets can work with compatible spreadsheet data exported from Access, but it does not repair Access queries.