Any table or query can be given a PivotTable view by selecting it from the View drop-down list (see Figure 14.1). But this is merely the launch point for creating a pivot table; your work is just beginning. The virgin PivotTable view in Figure 14.2 can be compared to the Design view that greets you when you build a new form from scratch—but with one vital difference. With a form, you build in Design view, but the display is in Form view. With a pivot table, you build in PivotTable view and display in PivotTable view. The main building blocks of a pivot table are the fields in the field list, which you drag and drop onto specific areas of the table.
These areas correspond to the four key elements in a pivot table: row, column, filter, and totals/detail (see Figure 14.2). These elements, particularly column and detail, work together slightly differently than they do in an Access table. A simple example is the best way to get a feel for how a pivot table works.
1. | Copy the NiftyLionsChap14.mdb database to your hard drive. | |
2. | ||
3. | Open the drop-down list on the View button at the far left of the toolbar and choose PivotTable View. Besides all the Orders fields, the field list includes By Week and By Month selections for fields with the Date/Time data type. (If you don't see a field list, choose View, Field List.) | |
4. | Click the + sign next to Order Date by Month. A full list of measures of time is displayed, including Years and Quarters. | |
5. | Select Years, and drag and drop it onto Drop Row Fields Here. In the list, note that Order Date by Month Field and Years Field now appear in bold. | |
6. | Click the + sign next to 2004 to expand the tree. Only Qtr3 is displayed because all orders were made in that period. If orders were made in all quarters, all would be displayed. | |
7. | Click the + sign next to Qtr 3 (see Figure 14.3). Figure 14.3. You can expand or collapse pivot table items by clicking the + or – buttons.The months are displayed. You could similarly expand the tree to display days, hours, minutes, and seconds (if the times had been entered with such specificity). | |
8. | Click Shipper in the field list. Drag and drop it onto Drop Column Fields Here. Note that dropping fields in the Column (or Row) section does not add values to the detail area. Instead, it gives you the column (or Row) headings. Also note that the table displays the ShipperID, the actual ID stored in the Orders table. If you built a query and included supplier company names on the design grid, you could then display the names in the pivot table.
| |
9. | ||
10. | Right-click ShipCost in the first column in the Detail area. Choose AutoCalc, Sum. The freight costs are summed by shipper and quarter (see Figure 14.4). Figure 14.4. Totals can be quickly added to the table using the AutoCalc command on the shortcut menu.
| |
11. | Click Overnight? in the field list. Drag and drop it onto Drop Filter Fields Here. You can use the Filter area for fields you might want to use as a filter but don't want to include on the table itself. For example, you might want to filter the records by customer state, but you don't want any state totals in the table. | |
12. | Click the Overnight? drop-down button. | |
13. | You select and deselect the check boxes to specify the values you want. The All choice shows all records. You can use the same method to filter from a row or column field, but you cannot filter from a totals/data field.
| |
14. | ||
15. | Click the drop-down list next to Shipper (see Figure 14.5). Figure 14.5. You can filter records using row, column, or filter fields.You can filter the Shipper records by similarly selecting and deselecting the records you want. | |
16. |
Let me spend a moment discussing the Hide Details and Show Details commands, which are important.
In a pivot table, you have two ways to present data. You can show the details, which are the actual values from the underlying table(s). You can also show totals, which includes sums, counts, and other aggregates.
When you drop a field onto the Drop Totals or Detail Fields Here area, the values (the details) are displayed. If you want to show totals instead, you can right-click the field name and choose an aggregate from the AutoCalc menu. (The Q&A in the upcoming section “Another Pivot Table Example” describes an additional method for calculating aggregates.)
When you don't want to see the actual field values, you can choose the Hide Details command from PivotTable menu; you can display them again by choosing the Show Details command.
As is often the case in Access, there are several ways to perform tasks. Here are a few of the more useful buttons on the Pivot Table toolbar (refer to Figure 14.5):
18.117.234.225