Show All

Create a PivotTable list on a data access page

1. In Design view of a data access page, click Toolbox on the toolbar.
2. In the toolbox, click the Office PivotTable tool .
3. Click the data access page where you want the upper-left corner of the control to be.
4. To choose a data source for the PivotTable list, do one of the following:

   Use the same data source as the page

   • Display the data access page's field list, and then drag to the PivotTable list the table or query you want to use for the PivotTable list. You can also drag individual fields from these record sources one at a time.

   When you create a PivotTable list by dragging fields from the field list, Microsoft Access automatically sets the Data source control option on the Data Source tab of the PivotTable list's Commands and Options dialog box to MSODSC, which means that Access uses the same data source control for the PivotTable list as for the page itself. It also sets the Data member, table, view, or cube name option to the name of the table or query on which you based the PivotTable list.

   Use other data source controls that are available on the page

   1. Click inside the PivotTable list to activate it.
   2. Right-click the PivotTable list and then click Commands and Options.
   3. On the Data Source tab, select Data source control, and then select the control that you want from the list.
   4. Under Use data from, click Data member, table, view, or cube name, and then select the element you want from the list.

   Use an external data source

   1. Click inside the PivotTable list to activate it.
   2. Right-click the PivotTable list, and then click Commands and Options.
   3. On the Data Source tab, select Connection, and then click Edit.
   4. Locate the data source you want to use in the Look in box, and double-click it in the list. If the data source you want to use is not available, click New Source and follow the instructions in the Data Connection Wizard to create a new connection file for your data.
   5. Under Use data from, do one of the following:
      • To use the data from a specific record source within the database, click Data member, table, view, or cube name, and then select the element you want from the list.
      • To use a query or command— such as an SQL statement— to select the data, click Command text or SQL, and then type the query or command in the box. For information about the query syntax, see the documentation for the external data source.

   Security Note Under some circumstances, a user can view the information used to connect to an external data source. This may represent a security risk. For more information, see About making connections to external data sources more secure.

5. Finish the PivotTable list by customizing the layout and using the toolbar and the Commands and Options dialog box in the PivotTable list to add totals, set properties, and so on, until the control looks and behaves the way you want.

For more help on how to design and use a PivotTable list, click Help on the PivotTable list toolbar. If the toolbar is not visible, select the Toolbar check box on the Behavior tab of the Commands and Options dialog box.

Notes

• To create a PivotTable list that uses data from a Microsoft Excel worksheet, you have to publish the data to a Web page from Excel. If you want to turn the Web page into a data access page, open it in Microsoft Access, and then add controls that are bound to Microsoft Access or Microsoft SQL Server database data.
• When you have a Microsoft Office Web Component bound to an external data source on the same data access page with controls bound to an Access or SQL Server database, the component control uses a different data source control than the rest of the page.
• You can create a chart that's linked to the PivotTable list so that when you change the PivotTable list, the chart updates automatically to reflect the changes.
• When you add a PivotTable list to a grouped data access page, a PivotTable list appears for each unique group and contains only those records for that group. For example, on a page grouped by year with a PivotTable list containing employee sales data, there is a PivotTable list for each year, containing only the sales data for that year. You can't display the PivotTable list for more than one group at a time.