Skip Maine state header navigation
A Publication Featuring The Information Services
Technology of Maine State Government
| Volume VII, Issue 10 | October 2004 |
|
|
This article describes the process used to analyze data for a paper to be presented at the National Onsite Wastewater Recycling Association conference in November. Access 2003 was used to prepare this paper, however the techniques may be appropriate to previous versions of the program.
One of the functions of the Wastewater and Plumbing Control Program is to maintain records of all subsurface wastewater and internal plumbing permits issued by the municipalities statewide. Microfiche records date back to 1974, with electronic data from 1990 also available. As 25% of the permit fee is submitted to the State to fund the program, an accounting program was developed to track the permits and monies. Originally developed in the DE RPG language running on a stand-alone IBM 5150 computer, the program was converted to dBASE III in the late 1980’s. The program currently runs as a Clipper compiled DOS program under Windows 2000 Professional, storing data in traditional dBASE III DBF files.
Permit data is entered daily and archived yearly, creating a series of DBF files, one for each processing year. For this project, Microsoft Access and Excel were used to combine and analyze the data and prepare line charts from the tabular data for the twenty year period from 1984 to 2003. A total of 191,197 valid records were analyzed, including some partial data from 1984-1989. The following steps were used to collate, validate, and analyze the permit data.
Conversion and Collation The first step was to bring the separate DBF files into Access and combine them into one MDB file. After creating a new, blank database the Get External Data - Import command from the File menu is used to bring the individual DBF files into the new Access database as individual tables (Figure 1).
Figure 1.
Each DBF file is selected and imported one at a time. In order to see DBF files in the dialog box, you must change the file type to dBase III. The operation can be cancelled when all the desired files have been imported.
Once imported, the individual tables can be appended to form one large table to simplify the data analysis. This is accomplished using the Append Query type. You might want to create a blank table with the same structure to use as the beginning table. Do this by highlighting one of the data tables and selecting Export from the File menu. Choose the name of your database as the export location and hit the export button. Type a name for the base table in the dialog box and select the "definitions only" radio button. This will create a blank table with the same structure as your imported data tables.
The simplest method is to begin a Query in Design view, selecting the tables which you want to append to the newly created blank table. Choose Append Query from the Query Type Icon and type the name of the blank table in the dialog box (Figure 2). Drag the star "*", which represents all fields in each of the tables to be appended to the query dialog area. This will cause the "append to" field to specify the name of the blank table. Hit the Exclamation or "Run" Icon and the append operation is completed. Repeat this for each of the individual data tables previously imported.
Figure 2.
Data Review After combining the data into one table three problems were evident: 1) changes to the permit form from which the data was captured complicated the analysis, 2) a lack of data validation made some records inaccurate, and 3) zeros in some of the fields instead of numerical values made some of the records useless for analysis. Access provides ways to help deal with items 1 and 3, which will be noted in Part 2 of this article which will be published in next month’s issue. Item 2 required a manual review of the data, utilizing the capability to sort a table by column in A-Z or Z-A order. Obvious corrections were made when possible, but some records were deleted as there was no way within a reasonable time period to validate the data.
Part 2 will also discus the use of pivot tables.
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.
