Microsoft KB Archive/275070

= ACC2002: Error When You Sort on a Column in a Crosstab Query =

Article ID: 275070

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q275070



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you try to sort on a column of a crosstab query, you receive the following error message:

Cannot use the crosstab of a non-fixed column as a subquery.



RESOLUTION
Save the query before you switch from Design view to Datasheet view. This allows you to successfully apply or remove a filter or a sort in Datasheet view of the query.



STATUS
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
 Open the Northwind sample database.  Create a new query, and then enter the following SQL statement in SQL view of the query: TRANSFORM Sum([Order Details].[UnitPrice]*[Order Details].[Quantity]) AS SumOfOrderItem SELECT Products.CategoryID, Customers.CustomerID FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE (((Products.ProductID)<20)) GROUP BY Products.CategoryID, Customers.CustomerID, Products.ProductName ORDER BY Products.CategoryID, Customers.CustomerID PIVOT Products.ProductName;  Save the query as qryCrosstab, and then close it. Open the qryCrosstab query in Datasheet View. Click the heading of the CustomerID column, and then on the Records menu, point to Sort, and click Sort Ascending. Note that the records are now sorted by CustomerID.</li> Switch the query to Design View, and then switch back to Datasheet View. Note that the query is no longer sorted by CustomerID.</li> Click the heading of the CustomerID column again, and then on the Records menu, point to Sort, and click Sort Ascending. Note that you receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.</li></ol>

<div class="references_section">