ACC: ORDER BYs Must Be Output Columns in DISTINCT Queries

- Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

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

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
   

REFERENCES