Create a PivotTable list on a data access page

Although you can rearrange the layout in a PivotTable list in a data access page and set properties to affect its look and behavior, you can't ever add, delete, or change the stored values in the PivotTable list.

1. In Design view of a data access page, display the toolbox.
   
   
2. Click the Office PivotTable tool in the toolbox.
   
   
3. Click on 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:
   • To use the current Microsoft Access or Microsoft SQL Server database as the data source, display the data access page's field list, and then drag to the PivotTable list control 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 in the PivotTable Property Toolbox to MSODSC, which means Microsoft Access uses the same data source control for the PivotTable list as for the page itself. It also sets the Data member option to the name of the table or query on which you based the PivotTable list. Go to step 6.
     
     
   • To use an external data source, display the PivotTable Property Toolbox, click the Data Source bar, click the Connection option, then click Connection Editor. Click the Provider tab, and then select the OLE DB provider you want to use to retrieve the data. Click the Connection tab, and then provide the connection information to the data that you want to use for the PivotTable list. For information about the Data Link Properties dialog box, click the Help button at the bottom of the dialog box.
5. In the Data Source section in the PivotTable Property Toolbox, under Use data from, do one of the following:
   • To use the data from a specific record source within the database, click Data member, 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, and then type the query or command in the box. For information about the query syntax, see the documentation for the external data source.
6. Finish the PivotTable list by arranging the layout and using the toolbar in the PivotTable list and the PivotTable Property Toolbox to add totals, set properties, and so on until the control looks and behaves the way you want.

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 Microsoft 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 Web Component control bound to an external data source on the same data access page with controls bound to a Microsoft Access or Microsoft 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.
  
  
• If you open the data access page property sheet, you'll see the specific properties associated with all the settings in the PivotTable Property Toolbox, along with other properties not available in the property toolbox.