Section 9.3. Pivot Tables

9.3. Pivot Tables If totals queries and crosstab queries just don't thrill you enough, Access has yet another high- powered feature for summarizing your data. A pivot table is a specialized table that performs the same tricks as a crosstab queryrow and column groupingbut has even more muscle. Here are some of the extra features: Pivot tables can be rearranged at any time . With a quick drag of the mouse, you can convert a sales-by-country summary to a sales-by-customer-age grid. That makes pivot tables great for data exploration , in which you try to ferret out hidden trends and relationships from an avalanche of raw data. Pivot tables support unlimited levels of grouping . You aren't limited to one level of column grouping, as you are in a crosstab query. Instead, you can sub-divide your rows and columns into smaller and smaller groups. Figure 9-10. Notice that the OrderQty field appears twice. The first time, it's defined as the value that appears in the table grid. The second time, it's defined as a row heading, which creates an extra column with the total for each row. Using an alias (Section 7.1.2), the extra column is renamed to Total Of OrderQty to prevent confusion. Pivot tables are collapsible . You can hide row and column groups you aren't interested in at the moment, and you can dig down into a group to see the individual records it contains. By browsing your data in this way, you can get a better idea of what's taking place with your data. Pivot tables support unlimited calculations . Crosstab queries can perform only a single calculation, which is repeated for each group. A pivot table can perform as many calculations as you want, and it stuffs them all into the same cell . Pivot tables support sorting by your calculated values . For example, if your pivot table adds up total sales, you can sort it so the best performers rise to the top. Note: Many Access fans lead long and happy lives without ever coming across a pivot table. That's because it's a fairly specialized tool, and many experts prefer to perform data analysis in another program (like Microsoft Excel). However, the pivot-table features are worth taking a look at, because they just may come in handy the next time you need to draw sweeping conclusions about how your celebrity-themed pastry company is performing. 9.3.1. Building a Pivot Table Access incorporates pivot tables in a rather unusual way. Unlike totals queries and crosstab queries, pivot tables aren't a specialized type of query. Instead, Access provides a pivot table viewing mode that you can use with any table or any query. Note: The reason for this seemingly odd design is because pivot tables are designed to be exceedingly flexible. With just a few mouse clicks, you can rearrange categories or drill down from the summary view to the individual records. In order for this to be possible, the pivot table needs to have the full set of records at its fingertips. To use the Pivot Table view, open the table or query you want to use, and then choose Home Views View PivotTable View. Alternatively, you can use the tiny view buttons at the bottom-right corner of the window to switch to the Pivot Table view with a single click. Initially, the Pivot Table view is empty (Figure 9-11). Figure 9-11. This example shows the Pivot Table view of the OrderedItems query. Currently, there's nothing to see, because you haven't yet built the pivot table. A Pivot Table Field List window pops up off to the side with all the fields in your table or query. Note: Pivot tables work only with a single table or query at a time. So it makes very good sense to create a query that joins all the tables you want, just as you did when you built your crosstab query. You can also use a query to create additional calculated fields (like a field that multiplies a product cost by the number of units). To create a pivot table, you need to tell Access what field to use for each part of the table. Every pivot table is made up of five ingredients : Row fields are used to group your records into rows. Column fields are used to group your records into columns. Data fields are used to calculate summary values for every group. Detail fields show individual values for every record in a group. Optionally, you can also show summary information (in which case the detail field acts like a data field). Filter fields are used to pare down the list of records used to create your pivot table based on the criteria you specify. Note: The structure of a pivot table is very similar to the structure of a crosstab querythe key difference is that many of the limitations that restrict crosstab queries don't apply to pivot tables. The easiest way to get comfortable with pivot tables and their many possibilities is to try your hand at building one. The following steps guide you through the process of creating a simple pivot table that shows a sales summary that's grouped by country and product category. If you want to follow along, use the OrderedItems query in the AdventureWorks database, which you can download from the "Missing CD" page at www.missingmanuals.com. You can then enhance the pivot table to take advantage of its extra features. Note: Prefer a more visual approach to learning about pivot tables? You'll also find a screencastan online animated tutorialon the "Missing CD" page. From the PivotTable Field List, drag the ProductCategory field onto the Drop Row Fields Here region . When you drop the field, Access fills in the names of all the product categories from top to bottom, in alphabetical order (see Figure 9-12). If you want to reverse your sort, just right-click one of the values, and then choose Sort Sort Descending. Show/Hide Field List. From the PivotTable Field List, drag the Country field to the Drop Column Fields Here region . When you drop the field, Access fills in the names of all the countries from the list from left to right, in alphabetical order. In other words, each country is listed in its own column. Figure 9-12. In this example, the list of products has already been added to the row area, and the second grouping criteria (the list of countries) is being dragged onto the column area. Notice that once a field is linked, its name is listed in boldface in the PivotTable Field List. Tip: If dragging and dropping is a little too awkward , there's another way to lay out a pivot table. In the PivotTable Field List window, simply select the field you want to add to the pivot table, and then, in the drop-down list underneath, choose where you want to place the field. Finally, click the Add To button (next to the list) to add the field. Now you need to choose what data you want to examine. Drag the OrderQty field over the "Drop Totals or Details Fields Here" region . This step fills the pivot table with data (although Access may need a few moments to group all the data if your table is extremely large). The OrderQty field is added as a detail , which means you see all the records in your table (or query), organized into their respective groups (Figure 9-13). It's time to add your summary calculations. Right-click the OrderQty field in the pivot table (any of the OrderQty fields will do), and then choose an option from the AutoCalc submenu . You can perform all the summary functions that you're familiar with, including sums, counts, and averages. For example, choose AutoCalc Sum if you want to find the total product quantity thats sold in a given category. All the summaries that you create with the AutoCalc submenu are known as totals . They're added to the PivotTable Field List window in a Totals group at the top of the list. (Click the +/-box next to the word Totals to open the Totals group.) To remove a total, right-click it in the list, and then choose Delete. Figure 9-13. This pivot table shows all the OrderQty values from the OrderedItems query, but it's a bit hard to analyze. To get an overview of what's really taking place, you need to hide some of these details and look at summary information instead. To hide all the details and see just the summary information, right-click the OrderQty field again, and then choose Hide Details . Once you've hidden the details, you get a respectable result that looks more like a crosstab query (Figure 9-14). Tip: If you know in advance that you don't want to see details (ever), you can add a total right away. To do so, select the field (in this case OrderQty) in the PivotTable Field List window, and then, in the drop-down list at the bottom of the window, choose Data Area. Then, click the Add To button. You can use this approach when working with huge tables that have thousands of records. In this situation, adding a total is faster than adding the full details from every record. Figure 9-14. With the details tucked out of the way, you can spot top-ranked and underperforming groups at a glance. The total for each row appears at the end of the row (not shown here), and the total for each column appears in the Grand Total row at the bottom of the column. 9.3.2. Manipulating a Pivot Table This is where the fun starts. One of the key benefits of pivot tables is their flexibility. There's just no limit to how many times you can move fields, how many levels of grouping you can throw into the mix, or how many different calculations you can perform at once. Here are a few ways to quickly change a pivot table: To remove a field , right-click it, and then choose Remove. Or, just drag it out-side the Access window (so the mouse pointer becomes an arrow with an "X"), and then let it go. To move a field from one position to another , just drag the field header to the correct region. For example, you can reverse the example shown earlier by dragging the column field (Country) to the row region, and then by dragging the row field (ProductName) to the column region. To sort a group , right-click anywhere inside the column for that group, and then choose Sort Sort Ascending or Sort Sort Descending. You can use this trick to find the countries and categories that sell the best (or make the most money). To add more data items , drag additional fields from the PivotTable Field List onto the table. For example, you can calculate the total quantity ordered and the average unit price. You can even add the same field more than once to per-form different summary calculations. Just drag the same field onto the pivot table, right-click it, and then choose an option from the AutoCalc submenu. To add more levels of grouping , drag additional fields from the PivotTable Field List onto the row or column regions . The trick is to make sure that you place the field where you want it in the grouping hierarchy. For example, if you want to subdivide your country groups into state groups, you need to drop the StateProvince field immediately to the right of the Country field, as shown in Figure 9-15. And if you want to subdivide your product category groups into individual product groups, you need to drop the ProductName field just to the right of the box for the ProductCategory field. Every time you change the structure of the pivot table, it rescans your table and rebuilds the entire pivot table. If you change the data in the table while the pivot table is open, you can choose PivotTable Tools Design Data Refresh Pivot to force Access to rebuild the pivot table right away. 9.3.3. Creating a Calculated Field To get some of the most interesting information from a pivot table, you often need to combine more than one field in an expression. The classic example (which you already saw with the crosstab query earlier in this chapter) is multiplying order quantity with product prices to determine the total sales. You can also multiply product prices with stock numbers to find the value of the inventory you have on hand. Figure 9-15. Top: Here, the StateProvince field is being placed to the right of the Country field. Columns will now be grouped by Country, and then subgrouped by StateProvince, which is what you want. Notice that Access shows a thick blue bar where the column will appear when you drop it. Bottom: Here, the StateProvince field is being placed to the left of the Country field. Columns are now grouped by StateProvince and then Country. Access lets you do this, but it doesn't make much sense. Because every StateProvince belongs to a single Country, each group will have exactly one subgroup , which is no help. This feat also works with a pivot table, but you need to do a bit more work. Here's what to do: From Pivot Table view (Section 9.3.1), choose PivotTable Tools Design Tools Formulas Create Calculated Detail Field . A multitabbed Properties window appears, with the Calculation tab currently visible (Figure 9-16). Figure 9-16. This calculated field performs the simple trick of multiplying two fields. In the Name box, enter a name for your calculated field . For example, you could enter TotalRevenue . In the large box underneath the Name box, enter the expression that performs the calculation . For example, you could enter [UnitPrice]*[OrderQty] . You can use any combination of Access functions and the fields in your under-lying table. (For a refresher on creating expressions for calculated fields, flip back to Section 7.1.) If you forget a field name, you can use the pull-down list under the text box. Just choose the field there, and then click the Insert Reference To button. Using the other tabs, apply any formatting changes you want for your field . The other tabs let you control how your calculated field works with other pivot tables (like filtering), and how the field's formatted. The most useful settings are on the Format tab, where you can set the font, the colors, and (most importantly) the number format. For example, it makes sense to set the number format for the TotalRevenue field to Currency, so it appears with the currency symbol that's configured for your computer, commas, and just two fractional digits. Click the Calculation tab (if you're not currently there), and then click Change to add your calculated field to the pivot table . If you used the Hide Details button to collapse your pivot table down to just summary information, you won't see anything in the pivot table. That's because the calculated field you just added is a detail field. To see the full list of values from every record, choose PivotTable Tools Design Show/Hide Show Details before continuing. You'll also see your calculated field appear in the PivotTable Field List. If you want to get rid of it later on, you can right-click the field there, and then choose Delete. The next step adds a more useful total for your detail field. Right-click your calculated field, choose AutoCalc, and then pick a summary option (like Sum). You can then right-click your calculated field and choose Hide Details to return to the more compact summary view . Your total field is added to the PivotTable Field List, under the Totals group at the top of the list. To delete it, right-click it there, and then choose Delete. To remove it from the pivot table but keep it around for possible later use, click the field on the table, and then choose Remove. And if you don't like the long name of the total (which is usually something like "Sum of TotalRevenue"), right-click it, and then choose Properties to open the Properties window. You can shorten the title in the Caption tab, in the Name box. Figure 9-17 shows the finished example. Figure 9-17. The pivot table shown here subdivides the country groups into states and subdivides the product categories into individual products. The pivot table also shows two totals fields: the total revenue (TotalRevenu) and the total number of units sold (OrderQty Sum). GEM IN THE ROUGH Putting Pivot Tables on Their Own Forms As you've already learned, a pivot table is just a fancy viewing mode on a table (or query). When you close the window after building a pivot table, Access asks if you want to save the "layout changes" you've made. At this point, you have two choices: choose Yes to store the structure of your pivot table with your table or query so you can review it later, or choose No to toss it away. If you choose No, the next time you switch to Pivot Table view you'll start out with a blank surface all over again, and you'll need to assemble your pivot table from scratch. If you're a true pivot table connoisseur, you may want to store two different pivot table views for the same table or query. That way, you can quickly take a look at your data from different perspectives. Unfortunately, each table and query can accommodate only a single pivot table. So what's an enterprising database developer to do? The answer is to create a separate form for your pivot table. Forms are custom windows that you create to make data entry and review easier. You'll study them in detail in Part Four. But for now, it's important to realize that you can take a single table and create an endless number of pivot table forms for it. If you plan to work with pivot tables in the long term , separating the pivot table from your data is invaluable. To create a pivot table form, follow these steps: In the navigation pane, select the table or query that has the data you want to use with your pivot table. Choose Create Forms More Forms Pivot Table. This shows the standard pivot table design surface. Drag and drop the fields to create your pivot table. Choose Office button Save when youre ready to save your pivot table (or just close the form, and Access will prompt you to save it). Either way, you need to supply a name for your form. Choose a name that clearly indicates your form is a pivot table, like SalesPivotTable. Later on, you can open the form from the navigation pane by double-clicking it. 9.3.4. Hiding and Showing Details As you've seen, pivot tables are a pretty darned helpful tool for creating detailed summary tables. The only problem is that sometimes pivot tables are too detailedleaving you with summaries that are nearly as detailed as the underlying table. For example, consider the pivot table shown in Figure 9-17. When you create this pivot table, you see the information about every product and every geographic region. But what if you want to show only a specific product, a product in a specific category, or products in a specific country or state? In this case, the trick is to hide every category you don't want to see using collapsing . The easiest way to expand or collapse information is to use the +/-buttons that appear next to the row headers and column headers (Figure 9-18). This technique lets you expand and collapse all the groups in a particular row or column. Figure 9-18. Use the plus (+) button to show the details for a collapsed group and the minus (-) button to hide the details for one that's expanded. In this pivot chart, all the product groups are collapsed except for Accessories. Also, the country Australia is collapsed , so you see only the totals (not the region-by-region breakdown). If you want to zero in on specific data more precisely, you can expand a single cell . In this case, just right-click the cell, and then choose Show Details. For example, using this technique, you can expand the cell that shows the clothing sales in Australia (rather than all the clothing sales or all the sales in Australia). 9.3.5. Filtering Pivot Tables Another way to simplify your pivot tables is to leave out some of the data that goes into building them. In order to do this, you use pivot table filtering , which is a lot like datasheet filteringyou tell Access what records you want to use and what ones you don't care about. You can use filtering in several ways. The two quickest filtering options are to choose the items you want to see from a list. Here are your choices: You can filter using the fields that group the rows and columns of your pivot table . For example, you can use this type of filtering to hide countries or product categories you aren't interested in. To apply this filtering, just click the drop-down arrow at the right of the appropriate field header (Figure 9-19). Then, turn off the checkmark next to each item you don't want to include in the pivot table. This is similar to collapsing parts of the pivot table (Section 9.3.5), except the information you filter out disappears completely. Not even the totals remain . Figure 9-19. The quick filtering feature lets you hide specific items you don't want in your pivot table. When this sort of filtering is in place, the drop-down arrow for the appropriate field turns from black to blue. You can filter using other fields in the source table . Just drag them from the PivotTable Field List to the Drop Filter Fields Here region just above the pivot table. Once you've added a filter field, a drop-down list appears next to the field header. Click the arrow to show the list of all values, and remove the check-mark next to the ones you don't want to see. Access also lets you filter for the highest or lowest calculated values in any group. For example, you can use this technique to hide sales data for poorly selling products. To try this out, right-click the header for the ProductName field, and then choose an option from the Show Top/Bottom Items submenu. Perhaps you want to see a fixed number of products (the best or worst 1, 10, 25, and so on) or a percentage (the best 1 percent, the best 10 percent, and so on). Figure 9-20 shows an example. Figure 9-20. Here, the pivot table is cut down to the bestselling five percent of all products. If you have categories that don't include a product in this range, these categories won't appear at all. (Incidentally, the top five percent of products leaves you with just two bike models.) Note: When top/bottom filtering is in place, an AutoFilter icon appears on the right side of the field header (it looks like a funnel). Hover over the icon to find out what filtering is in place. To remove the filtering, click the icon, and then choose AutoFilter. Choose it again to switch it back on any time. Top/bottom filtering is easy to apply, but if you have several levels of subgrouping , you need to take care to apply it in the right place. For example, consider the pivot table shown in Figure 9-20, which splits up sales by category and product name. If you apply the top/bottom filter to the ProductName field, you see the best one percent of all products. But if you apply the one-percent filter to the ProductCategory field, you'll see the best one percent of all categories. In other words, you'll focus on categories that have the most sales rather than hot products. To understand the difference, consider what happens if the Components category has a large number of slow-selling items that, when totaled together, add up to a lot. When you filter by ProductCategory, you see all the products in this top-performing category. But when you filter by ProductName, you focus on the most popular products and the categories that contain them. In this case, the Clothing category takes the spotlight with a few hot sellers. Tip: It's possible to tie yourself up in knots by applying too much filtering at once. If you've forgotten which filter settings you've applied, you can switch them all off at once by choosing PivotTable Tools Design Filter & Sort AutoFilter.