Microsoft KB Archive/278392

= Field list in Pivot Table or Pivot Chart view does not include fields from Subdatasheet =

Article ID: 278392

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q278392



Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you use the expand indicator (+) in Datasheet view to display related records in a subdatasheet, the fields from those records are not available for selection if you switch to Pivot Table or Pivot Chart view.



RESOLUTION
You can include fields from both tables by creating a query, a function, a view, or a stored procedure that joins the related tables, and then design the pivot table or pivot chart from that query.

To include fields from the Products table as well as from the Orders table in a Pivot Table view in the Northwind.mdb sample database, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.


 * 1) Start Access, and then open the sample database Northwind.mdb.
 * 2) Create a new query in Design view.
 * 3) In the Show Table dialog box, add the Orders, Order Details, and Products table to the query, and then close the Add Table dialog box.
 * 4) Double-click the title bar of the Products field list to select all the fields, and then drag them to the query design grid.
 * 5) Double-click the title bar of the Orders field list to select all the fields, and then drag them to the first column of the query design grid.
 * 6) On the View menu, click Pivot Table View.

Note that the fields from the Products table as well as from the Orders table are available for selection.



MORE INFORMATION
When you are working with a table in Datasheet view, you can use the expand indicator to display a subdatasheet that shows the records in a related table.

You can also work with the table in Pivot Table view so that you can create a cross-tab-type presentation of the table data, but the field list for the Pivot Table view does not include fields from the related table. This also occurs if you are working with Pivot Chart view.

Steps to Reproduce the Behavior

 * 1) Start Access, and then open the sample database Northwind.mdb.
 * 2) Open the Orders table in Datasheet view, and then click the expand indicator (+) to view the Order information for the first customer.
 * 3) On the View menu, click Pivot Table View.

Note that the fields that are available to design the pivot object do not include fields from the subdatasheet.

Additional query words: prb not there missing don't see can't

Keywords: kbtshoot kbdatabase kbprb kbdesign KB278392

-

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

© Microsoft Corporation. All rights reserved.