ACC: ORDER BYs Must Be Output Columns in DISTINCT Queries

ID: Q96895

The information in this article applies to:

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):

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.

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

Last Reviewed: March 20, 1999