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

• Microsoft Access 2.0

SYMPTOMS

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

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:

Q122927 WX1124: Microsoft Access Version 2.0 Service Pack

MORE INFORMATION

Steps to Reproduce Problem

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



2. 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]; 




3. 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:

Q114731 ACC: Shortcut Method for Creating an SQL Union Query

Additional query words: jet25 jet 2.5

Keywords : kbusage QryUnion
Version : 2.0
Platform : WINDOWS
Issue type : kbbug