Microsoft KB Archive/278374

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

Article ID: 278374

Article Last Modified on 4/4/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q278374



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

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.



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.

Notice that the full company name is displayed.
 * 1) 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.
 * 1) Drag the CompanyName field to the Row drop zone.

Notice that the full company name is displayed.



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.
 * 1) 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.
 * 1) 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.

Additional query words: prb look up ACC2002 ACC2003 ACC2007

Keywords: kbexpertiseinter kbtshoot kbpivottable kbprogramming kbdatabase kbview kbprb KB278374

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.