KB123587 - ACC2: Constant in ORDER BY Clause of Union Query Is Ignored

Article ID: 123587

Article Last Modified on 6/25/2002

Microsoft Access 2.0 Standard Edition

This article was previously published under Q123587

Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

When you run a union query containing an ORDER BY clause that references a constant, the constant value is ignored.

If the reference to the constant is the only item in the ORDER BY clause, no ordering of the data is performed. If the ORDER BY clause contains additional items, however, ordering by the other items is performed.

RESOLUTION

Create another query based on the union query, and then sort the data in the new query.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs with the Microsoft Jet database engine version 2.5, which is available with the Microsoft Access version 2.0 Service Pack. For information about how to obtain the Service Pack, please see the following article in the Microsoft Knowledge Base:

122927 WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Steps to Reproduce Problem

Start Microsoft Access and open the sample database NWIND.MDB.

Create a new union query using the following SQL statement:

      SELECT DISTINCTROW Employees.[Last Name], Employees.Title,
       "Emp" AS Source
      FROM Employees
      UNION SELECT DISTINCTROW Customers.[Contact Name],
       Customers.[Contact Title], "Cust"
      FROM Customers ORDER BY [Source];

Run the query. Note that the data is not ordered as you expect.

REFERENCES

For more information about using constants in union queries, please see the following article in the Microsoft Knowledge Base:

114731 ACC: Shortcut Method for Creating an SQL Union Query

Additional query words: jet25 jet 2.5

Keywords: kbbug kbusage KB123587