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

• Microsoft Access version 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:

   ARTICLE-ID: Q122927
   TITLE     : 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:

   ARTICLE-ID: Q114731
   TITLE     : ACC: Shortcut Method for Creating an SQL Union Query