Skip Maine state header navigation
A Publication Featuring The Information Services
Technology of Maine State Government
| Volume VII, Issue 11 | November/December 2004 |
|
|
This is the second part of my article on using Microsoft Access to analyze data. The permit data table includes fields such as issue date, application type, variance type, system type, etc. Presenting a count of the data by permit issue year for the various system aspects was the goal; the PivotTable form provided the solution. This form provides a spreadsheet-like tabulation of data, typically with time along one axis. This article will discuss using the PivotTable Form in Access 2003. Note that it is considered a Form, not a Report.
A PivotTable can be based on a table or a query. For this example I want to see a count of all the different permit types by the year of issuance. The HHE-200 Form recognizes (1) new, (2) replacement, (3) expanded, (4) experimental, and (5) seasonal conversion permit types; and I wanted to compare the number of new, replacement, and total permits by year. The permit_type field contains numerical values from 1 to 5 representing the various permit types, with some values equal to 0 and greater than five representing data entry errors.
After opening your database, select Forms from the left side of the dialog box and choose New from the top of the box. Using the PivotTable Wizard will allow you to select only the specific fields you want to use in the PivotTable. After selecting the table containing your data, use the Add button (>) to select the specific fields. To create a PivotTable you will need at least two fields that have some relationship, such as permit issue date and permit type. The Wizard will present you with a blank PivotTable and a list of selected fields. Refer to Figure 1.
Figure 1.
Whenever a date field is included, Access gives you several choices for use, including actual value or the year, quarter, month, week, or day portion. The year portion is the most useful, as it combines all values for a given year from a field value entered as DD/MM/YY. To create the PivotTable, drag the appropriate date value to the top column box labeled "Drop Column Fields Here". Drag the permit type field to the left row box labeled "Drop Row Fields Here". To fill in the detail area, click the Calculated Totals & Fields Button on the icon bar and select Create Calculated Detail Field. Refer to Figure 1. Close the dialog box and click the Autocalc Button and select count from the drop-down list. You will now have the numerical permit types in rows and the permit issue years in columns with each intersection cell containing a count of the number of permits of that type issued that year.
To make the information more meaningful we will hide irrelevant data and group related items to reach the desired result. The Row Heading includes a drop-down arrow which controls which rows are displayed and used in the calculation. A separate row is created for each unique permit type value, in this example 0-8. Initially, all values are displayed; by un-checking the 0 and 6-8 rows records that contain invalid entries are ignored. An alternate method is to create a select query prior to starting the PivotTable, using the query to select only the relevant records. Using the PivotTable method gives you the option to use or ignore the suspect records.
Access provides the capability to group rows or columns in the PivotTable. When grouped, the individual row or column values are combined and displayed as a single value. For this example the rows for values 1 and 4 are combined to represent new systems and rows representing 2, 3, and 5 are combined to represent replacement systems. Use the Ctrl-left mouse click to select non-contiguous rows and right mouse click on one of the highlighted rows. Select Group Items and the highlighted rows will be added to form a single item. Refer to Figure 2. Right mouse click again and select Properties to give the group a meaningful name. Note that when you group two or more items, the remaining items are grouped together. If you have more than two groups, repeat the process as necessary.
Figure 2.
At this point it is a matter of formatting the PivotTable to suit your needs.
Russell G. Martin, PE, F.NSPE is the Program Director of the Wastewater and Plumbing Control Division of Health Engineering, Department of Health and Human Services. He may be contacted by calling (207) 287-4735 or e-mailing russell.martin@maine.gov.
