ACC2002: The Upsizing Wizard Does Not Upsize Nested Queries If Base Queries Are Upsized as Functions

The Microsoft Access Upsizing Wizard does not upsize a nested query if both of the following conditions are true:

• The query includes a lower-level query that includes an ORDER BY clause in its SQL statement.
  
  -and-
  
  
• The lower-level query is involved in more than one join.

The Upsizing Wizard converts queries with ORDER BY clauses to functions. Therefore, the lower-level query is converted to a function. When a function is involved in more than one join in a query, the Upsizing Wizard does not upsize the query.

To work around this problem, use one of the following methods.

Method 1

Copy and paste the SQL statement from the nested query in your Microsoft Access Database to a new query in the upsized Microsoft Access project. To do so, follow these steps:

1. Open the database that contains the nested query.
2. Open the nested query in Design view.
3. On the View menu, click SQL View.
4. Select the entire SQL statement so that you can copy it.
5. On the Edit menu, click Copy.
6. Close the query. Do not save the changes.
7. Open the upsized Microsoft Access project.
8. In the Database window, click Queries under Objects, and then double-click Create view in designer.
9. In the Show Table dialog box, click Close.
10. On the View Design toolbar, click SQL to open the SQL window at the bottom of the screen.
11. In the SQL window, select the "SELECT FROM" text so that you can paste over the text.
12. On the Edit menu, click Paste. If necessary, edit the query so that it conforms to the proper T-SQL syntax.
13. Save the view and give it the same name as the query name in the Access database (.mdb) file.
14. Run the query.
15. Return to Design view.
16. In the Sort Type column, select the sort order for the field that you want to sort by.
17. Rerun the query.
18. Close and save the query.

Method 2

Before upsizing your database, open the lower-level query in Design view, remove the sort order, and then reinsert the sort order in the upsized query.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. Create a new query in Design view.
3. In the Show Table dialog box, click Close.
4. On the View menu, click SQL View.
5. Type or paste the following text into the SQL window:

      SELECT Employees.LastName, 
           Orders.OrderDate, 
           Orders.ShipCountry, 
           [Order Details Extended].ExtendedPrice, 
           Products.ProductName, 
           Categories.CategoryName
      FROM Employees  
        INNER JOIN (Categories 
           INNER JOIN ((Orders 
              INNER JOIN [Order Details Extended] 
        ON Orders.OrderID = [Order Details Extended].OrderID) 
           INNER JOIN Products 
             ON [Order Details Extended].ProductID = Products.ProductID) 
               ON Categories.CategoryID = Products.CategoryID) 
                 ON Employees.EmployeeID = Orders.EmployeeID;
   					

6. Save the query as qryNested, and then close it.
7. Upsize the database.
8. After the Upsizing Wizard is finished, note that the qryNested query was not upsized in the new Microsoft Access project.