| Pivot tables come with an array of features that either enhance your analytical power or simply make life easier and more pleasant. On the mundane side, you can change fonts and supply captions. On the much more interesting analytical side, you can create group items and filter for top (or bottom) values. Most of the fine-tuning is performed in the Properties dialog box, which is available from the context (shortcut or right-click) menus. The properties in the dialog box, as with those in the property sheet in a form, depend entirely on which element of the table you select. Using the Top Values Property As you might recall from Chapter 8, "Queries," you use the Top Values property to show the best, the worst, the highest, the lowest, and so on. You can set top and bottom limits for values in a pivot table as well. You might initially have some trouble deciding which field has the top values you want to see. The temptation is to choose one of the Detail fields because that's where all the data is. But as the following example shows, the solution lies elsewhere. 1. | From the Database window, open qryPivotFrieghtTopValues.
This is the same query as PivotFreight, with one more column: I've added the OrderID field.
| 2. | Open the View drop-down list and select PivotTable View.
The Order ID column is the third row field, after CustState. Because orders have only one freight cost, the subtotals in the ShipCost column are unnecessary (see Figure 14.12). You can easily hide the subtotals by using the Subtotal toggle on the shortcut menu.
Figure 14.12. The subtotals in the ShipCost columns are superfluous and can be easily eliminated. 
| 3. | Right-click Order ID. Click Subtotal to eliminate the subtotals.
| 4. | Right-click either occurrence of ShipCost for a shortcut menu.
Note that the Show Top/Bottom Items selection on the menu is grayed out and unavailable. Because ShipCost is a detail field and not a row or column, you cannot show the top values.
| 5. | Right-click Order ID and choose Show Top/Bottom Items, Show Only the Top, 5.
The pivot table hides all but the five orders with the highest freight costs. Notice that a little filter symbol now appears in the Order ID column (see Figure 14.13).
Figure 14.13. The Top/Bottom Values command hides all orders except those with the highest freight cost. 
| 6. | Right-click Order ID. Choose Show Top/Bottom Items, Show All.
| 7. | Close the query and save your changes.
|
Grouping Records Sometimes you want to group records by intervals. For example, you might want to group the records by OrderID number. 1. | From the Database window, open qryPivotFreightGrouping.
This is the same query as PivotFreightTopValues, but I've deleted the company name and customer state fields from the pivot table.
| 2. | Click the View drop-down list and choose PivotTable View.
| 3. | Right-click Order ID in the row area and choose Properties.
| 4. | Click the Filter and Group tab.
| 5. | In the Grouping section, open the Group Items By drop-down list and choose Numeric Interval.
| 6. | Edit the interval to 5.
In the Properties dialog box, note that you could have also supplied a range of orders.
| 7. | Close the Properties dialog box. The OrderIDs are now grouped in units of five (see Figure 14.14).
Figure 14.14. Grouping is just one of many properties you can set in the Properties dialog box. Here the OrderIDs have been grouped in intervals of five each. 
| 8. | Close the query and save your changes.
|
|
