| 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 scratchbut 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. Figure 14.1. PivotTable and PivotChart are views in a table, form, or query; they are not separate objects in the Database window. 
Figure 14.2. The Order Details table in PivotTable view before any fields have been added. (The field list has been moved and the table has been resized for a better picture; your screen will look slightly different.) 
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. | Open the tblOrders table and briefly familiarize yourself with the data.
Note that all the orders were made in the third quarter of the year. Also note that Overnight? is a Yes/No field.
| 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.
TIP When you select fields for columns, they should contain only a few different values. In other words, the fields can have lots of records, but the data in them should be highly repetitive, not highly unique. Otherwise, you'll find yourself with hundreds or even thousands of columns, and your PivotTable will be difficult to manage and interpret. | 9. | In the field list, click ShipCost. Drag and drop it anywhere on Drop Totals or Detail Fields Here.
The values from the underlying table are displayed.
| 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. 
TIP If the field list disappears, click anywhere in the table. You can also click View, Field List once (or twice) on the Pivot Table toolbar. | 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.
TIP As you can see, the filter tools in a pivot table are limited. One workaround is to create a filter with your usual methods (such as Filter by Selection) in Datasheet or Form view and then switch to PivotTable view. | 14. | Deselect Yes to show only those records sent by regular freight. Click OK.
The values in the pivot chart are adjusted accordingly.
| 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. | Click Cancel. Close the table. Click Yes to save the layout changes.
|
Details and Totals 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. Pivot Table Commands 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): AutoFilter Lifts the filter and displays all records. If you click AutoFilter again, the filter is restored. Hide Details Displays only totals. Show Details Displays both values and totals. Show As (Percentage) Displays numbers as various percentages of others. Field List Shows the field list.
|
