Error message when you run a query in Access: "Cannot group on fields selected with '*'"

Article ID: 835414 - View products that this article applies to.
This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file. Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 97 version of this article, see 103189
(http://support.microsoft.com/kb/103189/ )
.
Expand all | Collapse all

On This Page

SYMPTOMS

You may receive an error message when you run a query in a Microsoft Access database that uses one of the following aggregate functions:
  • Sum
  • Min
  • Max
The error message that you receive is as follow:
Cannot group on fields selected with '*'.

Back to the top | Give Feedback

CAUSE

This problem occurs when you use aggregate functions in the query, and you do not set the Output All Fieldsproperty of the query to No.

When you set the Output All Fieldsproperty of the query to Yes, an asterisk (*) wildcard character that represents all columns of the database table is added to the select clause of the Access query. However, you cannot have an asterisk (*) wildcard character together with an aggregate function in the select clause of the Access query. Therefore, when you run the Access query, you may receive the error message that is mentioned in the "Symptoms" section.
Back to the top | Give Feedback

RESOLUTION

To resolve this problem, set the Output All Fields property of the query to No, and then run the Access query that uses an aggregate function. To do this, follow these steps:
  1. Start Access.
  2. Open the Access database that contains the problem query.
  3. In the Database window, click Queries under the Objects section.

    Note In Access 2007, click the Queries group in the left Navigation Pane.
  4. Right-click the query that you want to modify, and then click Design View.
  5. On the View menu, click Properties.

    Note In Access 2007, click the Design tab, and then click Property Sheet in the Tools group.
  6. In the Query Properties dialog box, set the value of the Output All Fields query property to No.

    Note In Access 2007, click the Stored Procedure tab in the Property dialog box. Make sure that the Output all columns option is not selected.
  7. On the Query menu, click Run.

    Note In Access 2007, click the Design tab, and then click Run in the Tools group.
Back to the top | Give Feedback

MORE INFORMATION

You can use the Output all fields option to automatically include all the fields from the underlying tables in the results of the final query. You can also use the Output all fields option to automatically include all the fields from the queries in the results of the final query. When you do this, you do not have to add all the fields from the underlying tables or all the fields from the queries to the design grid.

To do this in Access 2003 and in earlier versions of Access, follow these steps:
  1. Start Access.
  2. In the Database window, click Options on the Tools menu.
  3. In the Options dialog box, click to select the Output all fields check box on the Tables/Queries tab.
  4. Click Apply, and then click OK.
In Access 2007, follow these steps:
  1. Start Access.
  2. Click Microsoft Office Button, and then click Access Options.
  3. Click Object Designers.
  4. Click to select the Output all fields check box under Query design, and then click OK.
Note When you change the Output all fields option, this only affects the property setting for new queries that you create. When you change the Output all fields option, this does not affect existing queries.

Steps to reproduce the problem in Access 2003

  1. Start Access.
  2. Open the Northwind.mdb sample database.
  3. Run the Order Subtotals query in the Northwind.mdb sample database to make sure that the Order Subtotals query runs successfully. To do this, follow these steps:
    1. In the Database window, click Queries under the Objects section.
    2. In the right pane, right-click the Order Subtotals query, and then click Open.
    3. On the File menu, click Close.
    Notice that the Order Subtotals query uses the Sum() aggregate function.
  4. Open the Order Subtotals query in Design view. To do this, follow these steps:
    1. In the Database window, click Queries under the Objects section.
    2. In the right pane, right-click the Order Subtotals query, and then click Design View.
  5. Click anywhere in the Query window outside the query grid and outside the field lists.
  6. On the View menu, click Properties.
  7. In the Query Properties dialog box, set the value of the Output All Fields query property to Yes.
  8. Close the Query Properties dialog box.
  9. On the Query menu, click Run.

    When you run the query, you may receive the error message that is mentioned in the "Symptoms" section.
Back to the top | Give Feedback

Properties

Article ID: 835414 - Last Review: September 17, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbexpertiseinter kbtshoot kbproperties kbdatabase kbquery kberrmsg kbprb KB835414
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top