ACC2: Error (-1310) Running Crosstab or Totalling Query (119077)

SYMPTOMS

When you run a query that contains aggregate (totals) functions, you receive the error message:

Reserved error (-1310); there is no message for this error.

This problem occurs only with queries for which all of the following conditions are true:

The query does not contain remote (ODBC) tables.
The query contains two or more tables and can be divided into two or more queries to obtain the same results.
The query does not have any criteria on the aggregate fields.

CAUSE

The error is caused by the query optimizer. Any changes made to the query or its underlying tables that cause Microsoft Access to change its query optimization strategy can either result in or resolve this error. Changes include adding criteria to the query, adding indexes to or removing them from underlying tables, adding rows to underlying tables, and then resaving the query, and so on.

RESOLUTION

Method 1

The best way to prevent the error is to divide the query into two or more separate queries that produce the same result. For example, the following sample query will result in the error message stated above. It uses tables from the sample database NWIND.MDB and returns the average unit price for each customer and the quantity of items sold:

   Tables: Customers, Orders, Order Details

      Group By: [Customers].[Company Name]
      Group By: [Order Details].[Quantity]
      Avg: [Order Details].[Unit Price]

To prevent the error, divide the query into two queries by following these steps:

Remove the Customers table from the query.
Drag the Customer ID field from the Orders table to the query grid.
In the Total row of the Customer ID column, select Group By.
Verify that the query runs correctly, then save it as TempTotals.
Create a new query based on the TempTotals query and the Customers table. Note that the table and query automatically join on the Customer ID field.
Drag the Company Name field from the Customers table, and the Quantity and AvgOfUnit Price fields from the TempTotals query, to the query grid.

Method 2

Adding criteria to the query can also prevent the error from occurring. Add criteria that do not affect the outcome of the query, such as comparing a field to itself. For example, you could use the following criteria for a query that includes the Customers, Orders, and Order Details tables:

   Customers.[Customer ID]=Customers.[Customer ID]

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.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

Open the sample database NWIND.MDB.
Create a new query based on the Customers, Orders, and Order Details tables. Note that the tables join automatically.
Drag the Company Name field from the Customers table to the query grid. Drag the Quantity and Unit Price fields from the Order Details table to the query grid.

From the View menu, choose Totals. In the Total row for the Company Name and Quantity columns, select Group By. In the Total row for the Unit Price column, select Avg.

Note that the SQL statement for this query is as follows:

    SELECT DISTINCTROW Customers.[Company Name],
        [Order Details].Quantity, Avg([Order Details].[Unit Price])
        AS [AvgOfUnit Price]
    FROM Customers INNER JOIN (Orders INNER JOIN
        [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID])
        ON Customers.[Customer ID] = Orders.[Customer ID]
    GROUP BY Customers.[Company Name], [Order Details].Quantity;

Run the query. Note that you receive the error message stated above.