Video: Pivot your data in Access

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

In addition to views like datasheet view and form view, Access provides PivotTable and PivotChart views to help you summarize and visualize your data. In this video, we create pivot views on a new query, add the PivotChart view to a form, and then add that form to a Navigation Form so that the PivotChart can be easily viewed by the users of the database.

Note    PivotTable and PivotChart views are not available in Access web databases. For more information about web databases, see the article Build an Access database to share on the Web.

In the video

Overview

Enable PivotTable and PivotChart views on a form

Create a PivotTable view

View and customize the PivotChart view

Set the default view for an object

Display a pivot view on a form or report

Add a form or report to a navigation form

Overview

You can create PivotTable and PivotChart views on Tables, Forms, and Queries. The data available for display in a PivotTable or PivotChart view depends on the object for which you are building the view.

  • When you create a pivot view for a form, the view can only display data from that form’s record source.

  • When you create a pivot view for a table, the view can only display data from that table.

  • Building pivot views on queries provides you with the most flexibility, because queries themselves are very flexible and can contain data from multiple tables.

Enable PivotTable and PivotChart views on a form

By default, PivotTable and PivotChart views are not enabled for forms. To enable them, use the following procedure:

  1. Right-click the form in the Navigation Pane, and then click Design View.

  2. If the Property Sheet is not already displayed, press F4 to display it.

  3. On the Format tab of the Property Sheet, set the Allow PivotChart View and Allow PivotTable View properties to Yes.

Top of Page

Create a PivotTable view

In this video, we create a query and then create PivotTable and PivotChart views for that query. However, the process is the same regardless of whether you are creating the views for a query, a form, or a table. The views can be created in any order, but in this video we start with the PivotTable.

  1. In the Navigation Pane, double-click the object for which you want to create the pivot views.

  2. On the Home tab, click View, and then click PivotTable View.

  3. If the PivotTable Field List is not already displayed, on the Design tab, in the Show/Hide group, click Field List.

  4. Determine which field or fields you want to have as row headings in the PivotTable, and drag them from the PivotTable Field List to the Drop Row Fields Here drop zone.

  5. Determine which field or fields you want to have as column headings in the PivotTable, and drag them from the PivotTable Field List to the Drop Column Fields Here drop zone.

  6. If you want to be able to filter data on certain fields, drag those fields from the PivotTable Field List to the Drop Filter Fields Here drop zone.

  7. Finally, determine which field for which you want to see detail or summary data, and drag it to the Drop Totals or Detail Fields Here drop zone.

By default, Access displays detail records unless you create totals. To create total rows and columns:

  1. In the PivotTable, click one of the detail values.

  2. On the Design tab, in the Tools group, click AutoCalc, and then select the type of total you want.

To hide the detail cells and only display the totals:

  1. Click a column heading for the detail field. For example, if the detail cells are based on the Amount field, click an Amount column heading.

  2. On the Design tab, in the Show/Hide group, click Hide Details.

To remove a field from the PivotTable:

  • Click the field’s column heading or row heading, and then on the Design tab, in the Active Field group, click Remove Field.

View and customize the PivotChart view

When you create a PivotTable view, a PivotChart view is also being created behind the scenes. However, you will probably need to customize the PivotChart view to make it display the data so that it’s easy to read. To view the PivotChart:

  • On the Home tab, click View, and then click PivotChart View.

Customize the chart using the tools on the Design tab. In this video, we do the following:

  • Switch the row and column headings    On the Design tab, in the Active Field group, click Switch Row/Column.

  • Change the chart type    On the Design tab, in the Type group, click Change Chart Type. Select the type of chart you want, and then close the Properties dialog box.

  • Display the legend    On the Design tab, in the Show/Hide group, click Legend.

  • Hide the drop zones    On the Design tab, in the Show/Hide group, click Drop Zones.

  • Format chart elements   Chart elements include such things as the background color and the Axis Titles. Select the chart element that you want to change, and then on the Design tab, in the Tools group, click Property Sheet. Make the changes you want, and then close the Properties dialog box.

Top of Page

Set the default view for an object

Once you have created pivot views for an object, you can set the Default View property so that one of the pivot views is displayed whenever you open the object. This ensures that the pivot view is displayed if you add the object to a form or report.

  1. Right-click the object in the Navigation Pane, and then click Design View.

  2. If the Property Sheet is not already displayed, press F4 to display it.

  3. If you are working with a query, click in a blank area of the query builder so that the Property Sheet displays Query Properties.

  4. Set the Default View property to PivotTable or PivotChart, depending on which view you want to display.

Top of Page

Display a pivot view on a form or report

  1. In the Navigation Pane, right-click the form or report to which you want to add the pivot view, and then click Layout View.

  2. Drag the object that contains the pivot view from the Navigation Pane to the form or report.

Note    If the pivot view is not displayed, make sure it has been set as the default view for the object (see previous section).

  1. Resize the pivot view until it looks the way you want.

  2. Save and close the form or report.

Top of Page

Add a form or report to a navigation form

In this video, we added a PivotChart view to a form, and then added that form to a navigation form that was created earlier. For more information about creating a navigation form, see the article Create a navigation form or the video Create a navigation form.

Top of Page

Applies To: Access 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language