Teach your users to analyze Access data with pivot tables and charts

Queries aren't your only option when analyzing Access data. Pivot tables and pivot charts can be just as useful and much faster. Here's a quick lesson that you can use to teach your Access users the power of pivot tables and pivot charts.

In “Teach users to manipulate data with Excel pivot tables” and “Present your Excel data with pivot charts,” I explained how to use pivot tables and pivot charts to analyze data interactively in an Excel spreadsheet. Until recently, the only way we could teach users to perform this type of analysis on Access data was to show them how to import their Access database into Excel using MS Query or teach them how to create a CrossTab query in Access. Both methods require students to spend a good deal of time learning about the intricacies of databases. And by the time they’ve learned that, they have often lost sight of their goal—getting the information they need quickly.

Fortunately, MS Office 97 changed all that. In the following example, I’ll show you how to teach your students to use the PivotTable Wizard in Access to analyze their data.

Use a small database
Start with a simple database like the one shown in Figure 1. All the records should be visible on the screen without scrolling. Likewise, use an absolute minimum of fields (i.e., only the ones needed in your pivot table demo). It is also a good idea to presort the database to make it easier for the students to check the pivot table results against the actual data in the table.

Figure 1
This database provides a range of employee information.

Start by building a form
After pointing out the various fields of data contained in this database, formulate a question that can be easily answered by the information in the table. In this lesson, we’ll use the question: What is the sum of all salaries by gender?

Because pivot tables are form objects in Access, you must first return to the Database Window by closing the Employees table. Next, click on the Forms button under Objects. To build a new form object, click on the New button in the toolbar (see Figure 2).

Figure 2
Click on New to begin building a new form object.

The New button will open the New Form dialogue box, shown in Figure 3. Select the PivotTable Wizard to create a form that includes an Excel pivot table. Then choose the table that will provide the pivot table’s data, which in this example is Employees.

Figure 3
This pivot table will draw its data from the Employees table.

After clicking OK, a screen appears that provides an introduction to using Excel PivotTable objects in Access forms (see Figure 4). Click on Next to continue.

Figure 4
The basics of using PivotTable objects in Access

The next dialog box asks which data fields will be needed for the PivotTable object. Since we are looking for salaries by gender, we only need to select the Gender and Salary fields (see Figure 5).

Figure 5
The Gender and Salary fields will be used for this PivotTable object.

Clicking Next brings us to the last dialog box of the PivotTable Wizard, shown in Figure 6. At this point, click on Finish.

Figure 6
One more click and the pivot table is created.

Building the pivot table on the form
We are now ready to begin building the pivot table onto the pivot table form. To begin, select the Edit PivotTable Object button (see Figure 7). This will activate Excel.

Figure 7
The Edit PivotTable Object button is at the bottom of the PivotTable Form window.

You can now build the pivot table as you would in Excel. First, refresh the data by clicking once on the Refresh Data button in the toolbar (the one with the red exclamation point). Next, click-and-drag the Gender field to the Drop Row Fields Here box. Then, click-and-drag the Salary field to the Drop Data Items Here box (see Figure 8).

Figure 8
Drop the fields into their appropriate locations.

The results appear immediately in the pivot table (seeFigure 9). You can now analyze the data further with a few mouse clicks.

Figure 9
The sum of all employee salaries by gender is now provided by the pivot table.

Suppose you want to know the average salary by gender. First, click on A3 and then on the Field Settings button in the PivotTable toolbar. This will bring up the PivotTable Field dialogue box. From here, select Average in the Summarize By scroll box (see Figure 10).

Figure 10
The pivot table will now display the average salary by gender.

After clicking OK, the following results are displayed (see Figure 11).

Figure 11
Note that the format of cells B5 through B7 has been changed to Currency.

Using Count
In a similar manner, you can compare the total number of female employees with the total number of male employees in the organization by choosing Count from the PivotTable Field box (see Figure 12).

Figure 12
By using Count, we can generate the total number of employees by gender.

The results of choosing Count are shown in Figure 13.

Figure 13
The total number of employees by gender

Don’t forget to save your work
When you are finished with your analysis, you can save the pivot table form. In this example, it is simply saved as PivotTable Form (see Figure 14).

Figure 14
This pivot table can be used again for further analysis as often as needed.

Once saved, you will not need to build it again. It can be used as often as you want to analyze the Employees table. To demonstrate this, double-click on the PivotTable Form object in the Database Window. When opened, the PivotTable Form shows the results of the last analysis (see Figure 15). To perform another analysis, click on Edit PivotTable Object, which will take you back to Excel. At this point, you will need to refresh the data by clicking the Refresh Data button in the PivotTable toolbar.

Figure 15
Click on Edit PivotTable Object and begin a new analysis.

Use a pivot chart to display your results
Now, let’s look at the following question: What is the minimum salary paid by gender? However, rather than working interactively with the PivotTable Form, let’s interact with a PivotTable Chart instead. First, click the Chart Wizard button in the PivotTable toolbar (see Figure 16).

Figure 16
Use a pivot chart this time to display your data.

The resulting chart is shown in Figure 17. Note that it displays the data for the previous analysis. To find the minimum salaries, first click on the Count of Salary field button in the upper left corner of the chart. Then, click on the Field Settings button in the PivotTable toolbar to display the PivotTable Field dialogue box.

Figure 17
A new analysis can be displayed in just a few steps.

Next, select Min from the Summarize By scroll box (see Figure 18).

Figure 18
The minimum salaries of employees will now be displayed.

After clicking OK, the graph changes dynamically and provides the minimum salary paid by gender (see Figure 19).

Figure 19
This chart illustrates the minimum salary paid by gender.

Ready to go
After this short lesson, your students should be ready to follow the step-by-step demo to build a new PivotTable form that will perform more complex analysis such as comparing the average salaries by gender for each division. This type of analysis would have taken much longer to teach with Access CrossTab queries. Using the PivotTable Wizard, however, students can begin analyzing their Access data within hours.
What do you think of Mary Ann’s lesson on using pivot tables and pivot charts in Access? Will this information help you teach your users? Your feedback is important to us. Post a comment or write to Mary Ann Richardson and let us know what you think.

Editor's Picks