ACC: ORDER BYs Must Be Output Columns in DISTINCT Queries (96895)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
This article was previously published under Q96895
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

If you create a query and use the DISTINCT predicate in conjunction with the ORDER BY clause, the fields that are being ordered must be output columns. If the ORDER BY fields are not output columns, you may receive the following error message:
ORDER BY clause ([Table Name].[Field Name]) conflicts with DISTINCT.

RESOLUTION

Use the DISTINCTROW predicate or remove the specified field from the ORDER BY clause.

MORE INFORMATION

Steps to Reproduce Behavior

Follow these steps in the sample database NorthWind (or NWIND.MDB in versions 1.x and 2.0):
  1. Create a new query and add the Customers table.
  2. Place the Address and City fields in the query design grid. 
          Query: MyQuery
      ----------------------
      Field Name: Address
         Sort: Ascending
         Show: False
      Field Name: City
         Show: True
  3. On the View menu, click SQL. Modify the SQL statement as follows:

    SELECT DISTINCT Customers.City
    FROM Customers
    ORDER BY Customers.Address;
  4. Run the query. Note that you receive the following error message:
    ORDER BY clause (Customers.Address) conflicts with DISTINCT
To resolve this, you must Show the Address field or use DISTINCTROW in place of DISTINCT in the SQL statement.

REFERENCES

For more information about using SQL predicates, search for "DISTINCT," and then "ALL, DISTINCT, DISTINCTROW, TOP Predicates (SQL)" using the Microsoft Access for Windows 95 Help Index.
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbprb kbusage KB96895
©2002 Microsoft Corporation. All rights reserved.