You may receive a "Cannot group on fields selected with '*'" error message when you run a query in Access (835414)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000

This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 97 version of this article, see 103189.

SYMPTOMS

When you run a query in a Microsoft Access database that uses one of the aggregate (totals) functions such as the Sum() aggregate function, the Min() aggregate function, or the Max() aggregate function, the query may not run successfully. You may receive the following error message:

Cannot group on fields selected with '*'.

CAUSE

This problem occurs when you use one of the aggregate (totals) functions in your query, and you do not set the Output All Fields query property to No. When you set the Output All Fields query property 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.

RESOLUTION

To resolve this problem, set the Output All Fields query property to No and then run the Access query that uses one of the aggregate (totals) functions. 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.
  4. In the right pane, right-click the problem 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 No.
  8. On the Query menu, click Run.

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, 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.

    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

  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.

REFERENCES

For more information about the Output All Fields query property in Access 2000 and Access 2002, click Microsoft Access Help on the Help menu, type Setting query properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the Output All Fields query property in Access 2003, click Microsoft Office Access Help on the Help menu, type Customize the display of fields in a query (.mdb) in the Search for box in the Assistance pane, and then click Start searching to view the topic.
Modification Type:MinorLast Reviewed:6/9/2004
Keywords:kbProperties kbDatabase kbQuery kberrmsg kbprb KB835414 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.