ACC1x: <Expression> Not Part of Aggregate Function or Grouping

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

If a select query that contains an expression is converted to a totals query, the following error message is displayed when the query is run:

   <expression> not part of aggregate function or grouping.

CAUSE

When a select query is converted to a totals query, all expressions are assumed to contain aggregate functions and cause the Total field to default to Expression.

RESOLUTION

To work around this problem, set the Total field to Expression and include an aggregate function as part of the expression for Field Name. If you cannot add an aggregate function, set Total to Group By as shown:

   Query: Query Name
   ------------------------
   Field Name: <expression>
   Total: Group By

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Behavior

Using the sample database NWIND.MDB, complete the following steps:

1. Open the Employee List query in Design view by choosing the Query button in the Database window, selecting the query name, and choosing Design.

2. From the View menu, choose Totals to convert the query to a totals query.

3. From the Query menu, choose Run. The following error message is displayed:

         "[Last Name] & ", " & [First Name]" not part of aggregate
         function or grouping.
   

4. In the query grid, make the following changes:

        Query: Employee List
        -----------------------------------------------------------
        Field name: Employee Name:[Last Name] & ", " & [First Name]
        Total: Group By
   

REFERENCES

Microsoft Access "User's Guide," version 1.0, pages 141-149