Because of the massive size of the State Secrets database (see page 3), we couldn't do cleanup in Access and I was the only person in our office with software powerful enough to handle the data (Visual FoxPro). So I came up with a way to have others help with the cleanup without having direct access.
I exported chunks of the data to Excel files, and had the others put the changes in a new field. Then I wrote a FoxPro program using the Excel copy down functions to apply the changes to the main data file. For further information, read on, then download an Excel file and FoxPro program file that shows this in action (you can open the FoxPro file with NotePad) from www.nicar.org/techtips.html.
For example, here's a list of names that needed to be standardized. In this case the first name field needs to be updated to the combined name:
ID LAST FIRST VA556 Smith John R VA557 Smith John VA558 Smith John & Jane CITY STATE NEW FIRST Reston VA John R & Jane Reston VA John R & Jane Reston VA John R & Jane
Then I added two new columns, putting the following pieces of code in them and copying down the length of the data: REPLACE ALL NEWFIRST WITH FOR ID=
So in this example, the two pieces of code are in columns G and H. The data (as shown above) is in columns A through F. In the next blank column, you concatenate together various pieces to write the code for a simple program. Here's what it would look like for this example:
=G2&
The result for the first line should look like this: REPLACE ALL NEWFIRST WITH "John R & Jane" for ID= "VA556"
To explain: The ampersand (&) is the tool to tie the pieces together. The first set of quotes provides a space between the word "with" and the "John R & Jane." The next set of quotes (it's four double quotes) is how you tell the computer you want to include the quotes in your answer something that you need in this case to tell the computer that "John R & Jane" is a text string.
When you copy down the concatenated formula, you get the appropriate ID numbers for each line without a lot of repetitive typing. Then copy just the field that has your answers and paste it into a PRG file in FoxPro. It will look something like this:
REPLACE ALL NEWFIRST WITH "John R & Jane" for ID= "VA556" REPLACE ALL NEWFIRST WITH "John R & Jane" for ID= "VA557" REPLACE ALL NEWFIRST WITH "John R & Jane" for ID= "VA558"
You can use this same trick to apply changes to more than one record at a time by looking for keywords. For example, we used this for coding the expenditures. I created a list in Excel of possible keywords that we might find listed in the purpose field (most of the list came from a group-by query on the purpose field).
We had previously decided what code each keyword would fall under, so I assigned those in the Excel file. Here's what it looked like:
MAIL A4 SALARIES A2 HOTEL A1 AIRFARE A1 OFFICE SUPPLIES A5 POLLING C4 DIRECT MAIL C6
Then, in the Excel file, I added the FoxPro commands, but this time using wildcards. Here's what the command looks like when completed:
REPLACE ALL PURPCODE WITH "A4" FOR "MAIL" $ PURPOSE
The dollar sign symbol tells FoxPro to find the term "MAIL" anywhere in the field called PURPOSE.
The coding program ended up being more than 400 lines long because I had so many possible keywords. I kept the Excel file throughout the coding process, and that proved to be worthwhile when we decided to tweak codes.
Instead of having to sort through the whole FoxPro program file, I simply went back to the Excel file, sorted or filtered it to find the codes I wanted (there might be anywhere from two to 50 lines for any given code) and made the changes. Then I re-copied the formula and pasted it into a new program file. It took maybe five minutes to make hundreds of changes.