Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 278374 - Last Review: April 4, 2007 - Revision: 6.1
PivotTables and PivotCharts do not show values found in lookup list or in value list in Access
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.
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:
- Create a new query in Design View.
- Add the Customers and Orders tables to the
query.
- 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.
- Open the query in Datasheet View.
Notice that the full
company name is displayed. - 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. - Drag the CompanyName field to the Row drop zone.
Notice that the full company name is
displayed.
Steps to reproduce the behavior
- Open the Northwind sample database.
- Open the Orders table in Datasheet View.
Notice that the full company name is displayed in Datasheet
View. - 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. - 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.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbexpertiseinter kbtshoot kbpivottable kbprogramming kbdatabase kbview kbprb KB278374 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate