Creating PivotCharts in Access 2003 - dummies

By Alan Simpson, Margaret Levine Young, Alison Barrows

You can display the information from any Access 2003 table, query, or PivotTable as a PivotChart. When you create a PivotChart, you actually create a special view of a form, and the PivotChart is stored in the form. The easiest way is by using the PivotChart AutoForm, which was new in Access 2002.

To create a PivotChart based on a PivotTable, open the PivotTable and choose View –> PivotChart View from the main menu. Access displays the same information contained in the PivotTable as a PivotChart. (See Figure 1.)

A PivotChart contains the same data as on a PivotTable.

Figure 1: A PivotChart contains the same data as on a PivotTable.

To create a PivotChart from any table or query, follow these steps:

1. Click the Tables or Queries button in the Objects list of the Database window and then select the table or query that contains the information you want to analyze.

If the Database window isn’t visible, press F11. You don’t need to open the table or query — just select it.

2. Choose Insert –> Form from the main menu and, in the New Form dialog box that appears, select the AutoForm PivotChart option and then click OK.

Access creates a new, blank form and a control containing the PivotChart. The Chart Field list also appears, showing the fields from the table or query on which the PivotChart is based, as shown in Figure 2. A new toolbar also appears: the PivotChart toolbar.

You create a PivotChart by dragging fields to the blank chart.

Figure 2: You create a PivotChart by dragging fields to the blank chart.

3. Specify what’s on your chart by dragging fields from the Chart Field list to the PivotChart drop areas.

If the Chart Field list doesn’t appear, click the Field List button on the PivotChart toolbar. The Chart Field list is like the Field list that appears when you edit a form: It lists the available fields to drag to the PivotChart.

The PivotChart drop areas are composed of

• Filter fields: Fields you want to use to filter the data shown in the chart. To filter the data, click the drop-down list and click to remove check marks — only checked data is included in the chart. This drop area works like the Criteria row in an Access query.

• Data fields: Fields containing the data you want to chart (for example, the numbers that are represented by the heights of the bars of a bar chart). The values of these fields are measured by the numbers on the Y-axis.

• Category fields: Fields that contain values that you want to run along the bottom edge (X-axis) of the chart.

• Series fields: Different values in these fields are represented by different lines in a line chart, different bars in a bar chart, or different colored graph elements. To display legends for the series, click the Show Legend button the PivotChart toolbar or choose PivotChart –> Show Legend from the main menu. If you want a stacked or clustered bar chart, a line graph with more than one line, or a multi-ring doughnut chart, drag more than one field to the Series drop area.

4. Make changes to the type of chart, which fields are graphed, and which values of each field are included.

5. To see the chart better, close the Chart Field list by clicking its X button.

You can always open it again if you want to add more fields: Right-click in the PivotChart and choose the Field List option from the shortcut menu that appears.

Saving and viewing your PivotChart

Like PivotTables, a PivotChart is a special view of an Access object (tables and queries have pivot views available to them, too). When you close a PivotChart, Access asks you to name the form that contains the PivotChart and then stores the form with your other forms. When you close a PivotChart view of a table or query, Access just saves the information as part of the table or query. You can save your changes while editing a PivotChart by choosing File –> Save or pressing Ctrl+S.

To avoid confusion, consider including the word “PivotChart,” “Pivot,” or “PC” in the names of forms that contain PivotCharts.

To open a PivotChart again, click the Forms button in the Objects list of the Database window and, from the list that appears in the right pane of the window, double-click the name of the form that contains the PivotChart. Access opens the form in PivotChart view. The title bar of the PivotChart View window shows the name of the table or query that provides the record source for the chart.

You can switch to other views by clicking the View button on the toolbar or choosing View –> Datasheet View, View –> Form View, View –> PivotTable View, or View –> Design View. Switching to PivotTable view shows the same information as rows and columns of text. Switching to Datasheet, Form, or Design views is usually pointless, though — you see only the datasheet or AutoForm of the underlying table or query.