|Microsoft Office Access 2003
Microsoft Access 2002
In addition to Datasheet and Form views, Access 2002 or later supports PivotTable and PivotChart views for viewing data in a datasheet or form. The PivotTable view uses an Office PivotTable Component and facilitates interactive data analysis. The PivotChart view uses an Office Chart Component and helps you create dynamic, interactive charts.
This article shows a sample PivotTable view and PivotChart view for the Invoices query in the Northwind sample database, and explains the steps required to create each view.
Designing a PivotTable view
Sample PivotTable view for the Invoices query
The following illustration shows the Invoices query in PivotTable view. The initial view shows only the summarized values.
The Country field is filtered to show data only for the United States.
The Custom Region field is a custom group field. Custom group fields allow you to group items the way you want for better data analysis. Here, the City items are grouped into two categories: West Coast and Rest of the Country.
Total Trans Value shows, as a percentage value, the total value of transactions handled by a salesperson in each of the two regions.
No. of Trans shows the number of transactions handled by a salesperson in each of the two regions.
Viewing detail data
You can click the box to the right of a salesperson or below a region to see individual detail records. The following illustration shows detail data for Anne Dodsworth.
These detail fields exist in the underlying record source.
A calculated detail field uses an expression based on other detail fields.
These individual transactions were handled by Anne Dodsworth in the West Coast region.
Summary fields and data appear at the bottom of the view.
You can click the box to the right of a salesperson or region to hide the corresponding detail data.
Note Hiding detail data improves the performance of the PivotTable view.
Viewing individual items
When a column area has more than one field, you can click the expand indicators ( and boxes) to the left of an item to view lower-level items. For example, to view summarized or detail data for each city on the West Coast, click the box to the left of West Coast.
The following illustration shows the summarized values for each city in the West Coast group.
Items of the City field that are grouped under the custom group West Coast.
Summary data for Elgin.
Clicking here will display customers based in Elgin and summary data for each customer.
Clicking here will display detail data for Elgin.
Create the sample PivotTable view
1. Open the Invoice query in PivotTable view.
- On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
- Under Objects in the Database window, click Queries.
- Click Invoices, and then click Open on the Database window toolbar.
- On the View menu, click PivotTable View.
You see a blank PivotTable view without any fields or data.
2. Add data fields to the PivotTable view.
- If the field list is not visible, click Field List on the toolbar.
- In the field list, click Country.
- In the text box next to the Add to button, select Filter Area, and then click Add to.
- In the field list, click City, select Column Area in the text box next to the Add to button, and then click Add to.
- Repeat the previous step to add the CustomerID field to the right of the City field.
- Add the Salesperson field to the row area.
- Add the Product Name, Unit Price, and Quantity fields to the detail area. To add fields to the detail area using the field list, select Detail Data in the text box next to the Add to button, and then click Add to.
3. Add calculated detail fields and total fields to the view.
- Click Calculated Totals and Fields on the PivotTable toolbar, and then click Create Calculated Detail Field.
- In the text area below the Name text box, type the following, and then click Change.
UnitPrice * Quantity
- Click and drag the label of the Calculated field to the right of the Quantity field.
- With the Calculated field selected, click AutoCalc on the PivotTable toolbar, and then click Sum.
- Click AutoCalc again, and then click Count to add another total field to the view.
Note A calculated field uses an expression, whereas a total field calculated with AutoCalc uses one of the built-in aggregate functions.
4. Change field captions and format data.
- Click Properties on the PivotTable toolbar, and then click the Captions tab.
- Select the label of the Calculated field in the view, and then type Value in the Caption text box in the Properties dialog box.
- Click Hide Details on the toolbar to hide the detail fields and show only the total fields.
- Click the label of the total field that uses the Sum function, and then type Total Trans Value in the Caption text box in the Properties dialog box.
- Repeat the previous step to rename the count total field to No. of Trans.
- In the Properties dialog box, click the Format tab.
- In the view, click the label of the Total Trans Value field, and then set the Number text box in the Properties dialog box to Currency.
This step is optional, because the next step will change the setting to show the total values as a percentage of row total.
- With the Total Trans Value field still selected, click Show As on the PivotTable toolbar, and then click Percent of Row Total.
5. Filter, sort, and group data.
- Click the black arrow next to the Country field in the filter area.
- Clear the (All) check box, select the USA check box, and then click OK.
Note You can filter fields in the row and column areas as well. Filtering row and column fields restricts the items that are displayed in the corresponding field, whereas filtering a field in the filter area restricts the data that is displayed in the entire view.
- In the view, click the names of the cities that you want to include in the West Coast region. For example, click Elgin, and then press and hold the CTRL key while you click Eugene, Kirkland, Portland, San Francisco, Seattle, and Walla Walla.
- Right-click a selected city in the view, and then click Group Items.
You have created a custom group level (City1) without modifying the underlying source data.
- Click City1 in the view, click Properties on the toolbar, and then click the Captions tab.
- In the Caption text box, type Custom Region.
- Click Group1 in the view, and then type West Coast in the Caption text box. Click Other (you may have to scroll to the right), and then type Rest of the Country in the Caption text box.
- Select the label of the Salesperson field, and then click Show Details on the toolbar.
- Select the Unit Price field, and then click Sort Ascending on the toolbar.
- Click Hide Details on the toolbar.
- Select the Custom Region field, and then click Collapse on the toolbar.
Designing a PivotChart view
Differences between PivotTable view and PivotChart view
The layout of a PivotChart view is similar to that of a PivotTable view, except that, by default, PivotTable views show data details while PivotChart views show data totals or summaries (usually in the form of sums or counts).
Also, instead of row and column areas, a PivotChart view shows series and category areas. A series is a group of related data points and is usually represented in the legend by a particular color. A category consists of one data point from each series and is usually represented by a label on the category (x) axis.
The layout changes that you make to a table, query, or form in other views are independent of the object's layout in a PivotTable or PivotChart view. However, a layout change that you make in PivotTable view, such as moving a field from the row area to the column area, is reflected in the PivotChart view, and vice versa.
PivotChart view examples
The first PivotChart view example illustrates the five salespeople who had the highest sales amounts (as indicated by total unit prices) in the United States between 1996 and 1998. Each data marker extends to the value on the value (y) axis that represents the sum of prices for units sold.
In the filter area, the Country field is filtered to show only USA unit prices.
The data area shows the totals for unit prices sold.
The plot area provides a background for the data markers.
Gridlines make it easier to see where data markers fall on the value axis.
This data marker represents one category point (in this case, a salesperson) made up of three series points (in this case, the years 1996, 1997, and 1998).
The series area contains the series field Order Date. A legend has been added to the right side of this chart and appears directly beneath the series field.
The category area consists of category labels; in this case, the category labels are salespeople's names.
The axis value label provides a frame of reference for each data marker.
The same chart can be "pivoted," or manipulated, to illustrate different sets of analyses, such as in the next example, which compares the top five countries/regions for orders placed. (Note that in this example, the Salesperson field is now in the filter area, while the Country field is in the category area).
Create the sample PivotChart view
The following instructions are based on the assumption that you have completed the above steps to create a PivotTable view.
1. Switch to PivotChart view.
- On the View menu, click PivotChart View.
2. Add and remove fields in the PivotChart view.
In this step, you'll remove some fields from the chart that are not useful for this example. (When you remove a field from a chart, the data is still available via the field list, but it is not displayed in the chart.) Next, you'll add a field to show order dates.
- Select Custom Region in the series area (on the right side of the chart), and press DELETE.
- Drag the City field in the series area to the chart border. This is another way to delete fields from the chart.
- Click CustomerID in the series area and press DELETE.
- Drag the No. of Trans field away from the chart to delete it.
- Click Field List on the toolbar.
- In the field list, click Order Date.
- In the text box next to the Add to button, select Series Area, and then click Add to.
3. Filter, sort, and group data.
In this step, you'll group the order dates by years so that it's easy to see summaries for each year at a glance rather than having to look at each individual order date. Then you'll filter the data to see which five salespeople had the highest sales amounts. Finally, you'll sort each data marker by its total value in ascending order so it's easy to see which of the five salespeople had highest sales and which had lowest.
- Right-click Order Date in the chart, and then click Properties.
- Click the Filter and Group tab.
- Under Grouping, in the Group items by list, click Years.
- Select a data marker in the chart.
- On the Filter and Group tab of the Properties dialog box, under Filtering, click Filtering in the Display the list, and then type 5 in the Items box.
- Right-click a data marker in the chart, point to Sort, and then click Sort Ascending.
4. Change the chart type.
- Right-click a blank area in the chart, and then click Chart Type.
- Click Column in the list on the left, and then click the stacked column chart sub-type in the box on the right. The stacked column chart is the second sub-type in the first row.
5. Add a legend.
- Click a blank area of the chart.
- On the toolbar, click Show Legend.
6. Format text.
- Select the category (x) axis title label (labeled Axis Title at the bottom of the chart), and then press DELETE.
- Select the value (y) axis title label (labeled Axis Title along the left side of the chart) and, if the Properties dialog box is not already displayed, click Properties on the toolbar.
- Click the Format tab, and, in the Caption box, type Unit Price.
- Under Text format on the same tab, click Bold.
- Click the name of a salesperson on the category (x) axis to select it. Click the name again to select all of the category (x) axis labels.
- In the Properties dialog box, click the Format tab, and then click Bold.
- In the Orientation list, click Upward.
7. Format the plot area.
- Click in the plot area to select it. The plot area is the gray box that contains the data markers.
- In the Properties dialog box, click the Border/Fill tab.
- Click the arrow next to the Fill Type box, and then click Gradient.
- Click the arrow next to the Color button, and click the color swatch for Blue (#0000FF). (To see the color names and codes, rest your mouse pointer on a color swatch.)
- Select a gridline in the chart.
- In the Properties dialog box, click the Line/Marker tab.
- Click the arrow next to the Color button, and then click the color swatch for White (#FFFFFF).
After following the procedures above, your PivotChart view should look similar to the first PivotChart view example above. You can make the PivotChart view look like the second example above by changing the position of the fields in the chart.
8. Change the view of data.
- In the field list, click Salesperson.
- In the text box next to the Add to button, select Filter Area, and then click Add to.
- In the chart, drag Country to the category area (the previous position of Salesperson).
- Click the blue arrow next to Country, select the (All) check box, and then click OK.
- Select any data marker in the chart.
- On the toolbar, click Show Top/Bottom Items. Point to Show Only the Top, and then click 5.
- If you need to re-sort the data right-click a data marker, point to Sort, and then click Ascending by Total.