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

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.

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.

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.

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.