Microsoft KB Archive/96895

= ACC: ORDER BYs Must Be Output Columns in DISTINCT Queries =

Article ID: 96895

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



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.



Steps to Reproduce Behavior
Follow these steps in the sample database NorthWind (or NWIND.MDB in versions 1.x and 2.0):

 Create a new query and add the Customers table.  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  On the View menu, click SQL. Modify the SQL statement as follows:

SELECT DISTINCT Customers.City

FROM Customers

ORDER BY Customers.Address; 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.

<div class="references_section">