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

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.

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.

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.