ACC2002: "Query Could Not Be Processed..." Error with Calculated Total in PivotTable List (301436)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q301436
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you create a calculated total within a PivotTable list, you receive the following error message: 
The query could not be processed:
   Formula error - cannot not find dimension member("<field name>") - 
   in a name-binding function

CAUSE

The expression you entered refers to a detail field from the field list of the PivotTable list. The expression used in a calculated total may only refer to another total.

RESOLUTION

There are different resolutions depending on the results that you want. You can summarize the results of a calculated detail field, or you can create a calculated total based on other totals.

Summarizing a Calculated Detail Field

If you want to summarize data for a calculated expression, create a calculated detail field first, and then create a total from the calculated detail field. The following example demonstrates how to calculate the extended price from the UnitPrice, Quantity, and Discount fields in the Orders and Order Details table in the Northwind sample database. This example first creates a calculated detail field named CalculatedPrice, and then a total that sums the calculated field.
  1. Open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Queries.
  3. Click the Invoices query in the Database window, and then click Open.
  4. On the View menu, click PivotTable View.
  5. On the View menu, click Field List to display the PivotTable field list.
  6. In the field list of the PivotTable list, drag the following fields to the following locations:

    FieldDrop Zone
    SalesPersonDrop Row Fields Here
    OrderDate By Month (Years)Drop Column Fields Here
    OrderIDDrop Totals or Detail Fields Here


    This should display the OrderID field grouped by SalesPerson and OrderDate by Month (Years).
  7. On the PivotTable menu, point to Calculated Totals and Fields, and then click Create Calculated Detail Field. This will display the Calculation tab in the property sheet.
  8. In the Name box, type CalculatedPrice.
  9. Enter the following expression in the box under the Name: 
    (UnitPrice * Quantity)*(1-Discount)
    NOTE: You can manually type this expression, or you can add the field names by selecting them in the drop-down box, and then clicking the Insert Reference To button.
  10. Click Change. Note that the PivotTable list displays a calculated detail field that calculates the revenue for each line item in the order, minus a discount.
  11. Close the property sheet.
  12. Click in one of the CalculatedPrice columns in the PivotTable to select it. Note that this automatically selects all CalculatedPrice columns in the PivotTable list.
  13. On the PivotTable menu, point to AutoCalc, and then click Sum. This will create subtotals that calculate the sum of the expression for each column and row.
  14. Click in one of the OrderID columns in the PivotTable list to select it. Note that this automatically selects all OrderID columns in the PivotTable list.
  15. On the PivotTable menu, point to AutoCalc, and then click Count. This will calculate the count of Order Detail records.
  16. On the PivotTable menu, click Hide Details. This hides the detail fields, so that only summary data is visible.
  17. Click in one of the Sum of CalculatedPrice columns to select it. Note that this automatically selects all Sum of CalculatedPrice columns in the PivotTable.
  18. On the View menu, click Properties.
  19. Click the Format tab in the property sheet.
  20. Set the NumberFormat property to Currency and then close the property sheet.
Note that the PivotTable list displays the sum of the calculated detail field that you created in step 10.

Calculating a Total Based on Other Totals

If you need to create a calculated total that relies on other totals, you must create the other totals first. Then, you can refer to those totals within the calculated total expression. The following example builds upon the steps in the "Summarizing a Calculated Detail Field" section earlier in this article.
  1. If you have not done so, follow all the steps in the "Summarizing a Calculated Detail Field" earlier in this article. This will create a PivotTable list on the Invoices query that calculates a detail field, and then sums it.
  2. On the PivotTable menu, point to Calculated Totals and Fields, and then click Create Calculated Total.. This will display the Calculation tab in the property sheet.
  3. In the Name box, type AveragePricePerDetail.
  4. Calculate the average price by dividing the Sum of CalculatedPrice(total) by the Count of OrderID(total). To enter the expression, use the drop-down box at the bottom of the property sheet to select each total, and then click the Insert Reference To button. The final expression in the box should appear similar to:
    [Measures].[Total1]/[Measures].[Total2]
  5. Click the Change button.
Note that the PivotTable list displays the results of the AveragePricePerDetail calculated total.

STATUS

This behavior is by design.

MORE INFORMATION

A PivotTable list total uses an aggregate function, such as Sum, Count, Average, and so on, for summarizing data in a detail field. This is similar to using a Microsoft Access totals query, and selecting an aggregate function in the Totals under a particular field.

A PivotTable list calculated total is an expression that requires one or more PivotTable list totals that use aggregate functions. You cannot refer to PivotTable list detail fields in a calculated total; if you do, you will receive the error listed in the "Symptoms" section earlier in this article. This behavior is similar to trying to use an aggregate function within a Microsoft Access select (non-totals) query. A calculated total is useful when you have already used aggregate functions to summarize data in the PivotTable list, and you then need to perform a calculation with the results of those totals. For example, you could calculate an average by dividing a total that summed a field, by a total that counted another field.

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. Follow steps 1 through 6 of the "Summarizing a Calculated Detail Field" section earlier in this article. These steps will create a PivotTable list on the Invoices query, and will display the OrderID field grouped by SalesPerson and OrderDate by Month (Years).
  2. On the PivotTable menu, point to Calculated Totals and Fields, and then click Create Calculated Total. This will display the Calculation tab on the property sheet.
  3. In the Name box, type Price.
  4. Enter the following expression in the box under the Name:
    (UnitPrice * Quantity)*(1-Discount)
  5. Click Change.
Note that you receive the following PivotTable error: 
The query could not be processed:<BR/>
   Formula error - cannot not find dimension member("UnitPrice") - 
   in a name-binding function
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kberrmsg kbprb KB301436
©2003 Microsoft Corporation. All rights reserved.