PivotTables and PivotCharts do not show values found in lookup list or value list (278374)


The information in this article applies to:

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

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

SYMPTOMS

When you view a table, a query, or a form that is using a lookup field in PivotTable or PivotChart view, the actual data stored in the field is displayed instead of the data from the lookup field.

CAUSE

The Office Web Components are used when rendering a PivotTable or PivotChart, and Microsoft Access passes the actual data stored in the fields rather than the lookup data.

RESOLUTION

To resolve this behavior, create a query that joins the appropriate tables, and use the actual columns in the query instead of using columns that are lookup fields. Use this query for the PivotTable or PivotChart. For an example of how to do this, follow these steps:
  1. Create a new query in Design View.
  2. Add the Customers and Orders tables to the query.
  3. Include all fields from the Orders table and only the CompanyName field from the Customers table. Click to clear the Show check box for the CustomerID field from the Orders table.
  4. Open the query in Datasheet view and note that the full company name is displayed.
  5. On the View menu, click PivotTable View.
  6. Drag the CompanyName field to the Row drop zone. Note that the full company name is displayed.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the Northwind database.
  2. Open the Orders table in Datasheet View.
  3. Note that the full company name is displayed in Datasheet view.
  4. On the View menu, click PivotTable View.
  5. Drag the Customer field from the field list to the Row drop zone. Note that the Customer field displays the customer ID instead of the full company name.
Modification Type:MinorLast Reviewed:6/7/2004
Keywords:kbPivotTable kbProgramming kbDatabase kbView kbprb KB278374
©2004 Microsoft Corporation. All rights reserved.