PivotTables

For the very limited type of PivotTable that can be created in Access 2000, see the Sales Analysis form in the original Northwind database that comes with Office 2000. To edit an Access 2000 PivotTable, you have to open Excel in a full window; on this form, click the Edit Pivot Table button.

There are several ways to create a PivotTable in Access 2002 or higher. You can switch to PivotTable view from a form, query, or report (I don’t recommend this except for a query that you have prepared for use with PivotObjects); create a new form, and select AutoForm: PivotTable (see Figure 5.2); or select PivotTable Wizard from the same list of selections. Selecting AutoForm: PivotTable opens a form with the selected query or table as its data source, in PivotTable view, ready to place fields in its drop zones.

A drop zone (also called drop area) is one of four labeled areas in a PivotObject where you can drop a field from the field list.

Figure 5.2

If you choose PivotTable Wizard on the New Form dialog instead, after an unnecessarily complex and confusing opening screen you will be asked for the fields you want to include in the PivotTable, and then the blank PivotTable will open. Because (if you follow my recommendations) you will have already prepared a query with suitable fields, this step isn’t necessary. I recommend just selecting AutoForm: PivotTable instead, and using drag and drop to move fields to the PivotTable’s drop zones as needed.

You will only use three to five fields at any time on a PivotTable, but there is an advantage to having more fields available in the field list. It gives you the flexibility to modify the PivotTable to display different data—and flexibility is what PivotTables are about!

I use the tag fpvt for PivotTable forms.

A PivotTable requires at least three fields: One for the row headings, one for the column headings, and one for the data in the center of the table. But (unlike crosstab queries) you aren’t stuck with your initial choices; with a PivotTable, you can change your selections interactively at any time.

The new PivotTable with qryNorthwindEverything as its data source is shown in Figure 5.3. It has labeled drop zones where you can drop row fields (the left edge), filter fields (the top), column fields (under the top), and detail or totals fields (data fields) (center). The PivotTable field List displays all the fields in the query, so you can drag them to the PivotTable.

Figure 5.3

You can see the advantage of preprocessing data for a PivotObject in the field list—you have a choice of Year, Month, or Quarter for date columns, and Customer, Supplier, and Shipper are separate, easily identifiable selections (remember, the field is actually CompanyName in all three tables). With a picture in your mind of how you want to see the data, all you need to do is drag the field(s) of your choice to each area. To see food categories on the left, drag the CategoryName field to the Rows drop area. To see results for salespersons by category, drag the Salesperson field to the Columns drop area. To filter by country, drag the Country field to the Filter drop area. Finally, to show price data in the data area of the PivotTable, drag the Price field to the Totals/Details drop area. The initial PivotTable created by these actions is shown in Figure 5.4.

Figure 5.4

Just as it is, with no more tinkering, this PivotTable is very useful—and very flexible. Unlike the limited Access 2000 PivotTables, you don’t need to open Excel to modify a PivotTable; you can adjust it right in Access PivotTable view. Note that the field selected for the rows, columns, and filters has a drop-down arrow next to it; clicking on this arrow lets you select values for filtering the data. For example, drop down the Country selections, and check only UK to see just UK data, as shown in Figure 5.5.

Important

To deselect all the values before selecting one, uncheck the All box.

Figure 5.5

After selecting UK, only results for salespersons based in the UK are displayed in the PivotTable. Similarly, you can filter for specific categories or salespersons. If you just want to see results for Steven Buchanan and Michael Suyama for the Beverages and Dairy Products food categories, select Steven Buchanan and Michael Suyama in the Salesperson drop-down list, and Beverages and Dairy Products in the CategoryName list. The filtered PivotTable is shown in Figure 5.6.

Figure 5.6

This basic PivotTable displays the prices for all the orders for Beverages and Dairy Products placed by Steven Buchanan and Michael Suyama. It is very useful as is, but when I look at it I can see some ways it could be improved—and because it is a PivotTable, it is just a matter of a few mouse clicks to customize it just as I prefer.

For example, I think it would be useful to have the order date alongside the price. All I have to do is drag the OrderDate field from the Field List to the data area, placing it to the left of Price. Now I have more filter options, with drop-down arrows next to OrderDate and Price.

Important

If the Field List disappears (as it does every once in a while, all on its own), make it visible again by clicking the Field List button on the PivotTable toolbar or by right-clicking the PivotTable and selecting Field List from the context menu.

I wanted to show orders only for 1995; to do this I dragged the OrderYear field to the data area (one of the date expressions created in the data source query) to the left of Order Date, so that I could filter by year. Figure 5.7 shows the PivotTable, filtered for 1995 orders.

Figure 5.7

After selecting 1995 as the filter value for OrderYear, there is no need to see the OrderYear column, so I used the standard datasheet column resizing arrow in the column heading to shrink the OrderYear column to nothing, and I also widened the Price column somewhat, to display all of the larger numbers.

Important

You won’t see the familiar Undo button when working on PivotObjects; if you make a mistake, you have to fix it manually.

Say that I don’t want to see each order separately, but just the total for each salesperson, and I also want to see Grand Totals (note that the initial PivotTable has GrandTotal areas, but no totals are displayed in these areas). To create Price totals, I select the Price column by left-clicking its header, click the Sum button on the PivotTable toolbar, and select Sum from the list of aggregate functions (they are similar to the choices in a totals query). (Alternately, I could just drag the Price field to the Grand Total column to create a sum—but if I want a choice of aggregate functions, the toolbar button provides more selections.) Now the PivotTable displays the sum of orders for each salesperson, each food category group, and the grand total in the lower-right corner. To see daily order totals, click the tiny plus (+) sign under the Grand Total column; Figure 5.8 shows a portion of the PivotTable with column and daily totals.

Figure 5.8

To see just the totals, without details, I clicked on the tiny minus (–) signs for each salesperson, each food category (you can make different selections for each column and row, if desired), and the grand total.

Important

To remove a total, right-click the total, then select Remove from its context menu.

If the plain grayscale PivotTable isn’t fancy enough for you, select fonts and colors that please you by clicking the element you want to change and selecting a font, size, and text color from the Formatting (PivotTable/PivotChart) toolbar. The formatting you select will be applied to all the elements of that type. For example, I selected the total under Michael Suyama’s column, and made it 11 pt, bold, and red; the result was that all the column totals had this formatting. I made the salesperson and category names 11 pt, bold, and bright blue on a white background, and the grand totals 12 pt and bold underlined. The resulting formatted PivotTable is shown in Figure 5.9.

Figure 5.9

How about swapping rows and columns? With a PivotTable, this is delightfully simple. All you have to do is drag the Salesperson field to the row drop area (drop it when you see the blue vertical bar, as shown in Figure 5.10) and then drag the CategoryName field to the column drop area.

Figure 5.10

After swapping, initially the details are shown; you can return to the summary display by clicking the tiny minus (-) sign in the row headings again. Curiously, after swapping rows and columns, the column headings revert to the default gray background, so I had to reapply the white background. Figure 5.11 shows the swapped PivotTable, with details displayed.

Figure 5.11

When you add Totals to a column, each column has its own total, as well as the grand total, displayed in the lower-right corner. There is also a Grand Total column at the right of the PivotTable, with a subtotal for each salesperson (as shown in Figure 5.11). If you don’t want to see this column, you can click the Subtotal button on the PivotTable toolbar to hide it. This button is a toggle button, so clicking it again makes the Grand Total column reappear.

The PivotTable Toolbar

PivotTables have their own toolbar, with several buttons you can use to modify PivotTable elements. Some of them will be familiar to you from other toolbars (query and datasheet); others are unique to PivotTables. The PivotTable toolbar is shown in Figure 5.12, and its buttons are explained in the table that follows the figure.

Figure 5.12

Note that some toolbar buttons are only enabled if the appropriate PivotTable element is selected; for example, you can only sort if a column is selected, and you can only use expand or collapse if there are two (or more) row fields or column fields.

Toolbar Button	Function	Comments
	Switch to another view	Rarely, if ever needed for PivotTables
	Save	Saves the form
	Search	Opens the Search pane
	Print	Prints the PivotTable
	Preview	Previews the PivotTable
	Copy	Copies the selected object
	Sort Ascending	Row or Column selected
	Sort Descending	Row or Column selected
	AutoFilter	Switches between the selected filter and showing all
	Show Top/Bottom Items	Shows the top or bottom n records; Row or Column selected
	AutoCalc	Creates a standard aggregate function; Row or Column selected
	Subtotal—toggles Grand Total column (with row subtotals) on or off	Row or Column must have at least two detail items; there must be a Totals field already
	Calculated Totals and Fields	Lets you create calculated expressions and totals
	Show As	Lets you show a total as a percentage of a selected total
	Collapse	Hides lower level—select the higher of two row or column levels
	Expand	Displays lower level—select the higher of two row or column levels
	Hide Details	Hides all the details; click the – sign on a row or column to just hide details for that row or column
	Show Details	Shows all the details; click the – sign on a row or column to just show details for that row or column
	Refresh	Refreshes data; useful when a PivotTable is based on a remote data source
	Export to Excel	Exports the PivotTable to Excel
	Field List	Displays the field list
	Properties	Lets you set many properties of various Pivot-Table elements; different properties can be set depending on which element is selected
	Database Window	Shows the database window
	New Object selector	Lets you create a new object
	Access Help	Opens Access Help

The PivotTable Properties Sheet

To fine-tune your PivotTable, open the properties sheet for any element and make changes (the PivotTable properties sheet has different pages and options depending on the element selected when it is opened). For example, to put a space between Category and Name for the CategoryName field, select the CategoryName field and open the properties sheet. Click the Captions tab and place the space between Category and Name in the Caption field, as shown in Figure 5.13.

Figure 5.13

You can use this page of the properties sheet to modify font and color properties as well, though it is generally easier to use the toolbar buttons for that purpose. The Format page of the properties sheet lets you make adjustments to the format of the selected element, as well as sort by Ascending or Descending (again, most of these properties can also be adjusted from the toolbar). The Filter and Group page lets you set up more elaborate filtering and grouping, and the Behavior page lets you set options related to the drop zone indicators and expand indicators (for example, if you are preparing a PivotTable for a presentation, you may wish to turn off these features).

Important

I have found that when I change the Caption property of a row or column, or the filter field name in a PivotTable, often the change doesn’t stick—it reverts to the original name either immediately after changing it or after closing and reopening the PivotTable. For this reason, for PivotObjects alone, I recommend not giving controls the usual LNC tags.