Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PivotTables and PivotCharts do not show values found in lookup list or in value list in Access


View products that this article applies to.

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.

↑ Back to the top


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.

↑ Back to the top


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.

    Notice that the full company name is displayed.
  5. On the View menu, click PivotTable View.

    Note In Access 2007, click the Datasheet tab, click the arrow under View in the Views group, and then click PivotTable View.
  6. Drag the CompanyName field to the Row drop zone.

    Notice that the full company name is displayed.

↑ Back to the top


More information

Steps to reproduce the behavior

  1. Open the Northwind sample database.
  2. Open the Orders table in Datasheet View.

    Notice that the full company name is displayed in Datasheet View.
  3. On the View menu, click PivotTable View.

    Note In Access 2007, click the Datasheet tab, click the arrow under View in the Views group, and then click PivotTable View.
  4. Drag the Customer ID field from the PivotTable Field List to the Row drop zone.

    Notice that the Customer ID field displays the customer ID instead of the full company name that is shown in Datasheet View for the Order table.

↑ Back to the top


Keywords: KB278374, kbprb, kbview, kbdatabase, kbprogramming, kbpivottable, kbtshoot, kbexpertiseinter

↑ Back to the top

Article Info
Article ID : 278374
Revision : 7
Created on : 4/4/2007
Published on : 4/4/2007
Exists online : False
Views : 444