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

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

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.

Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0
Platform : WINDOWS
Issue type : kbprb