This post picks up where Part 1 left off.
There are two ways to open Pendragon Forms: using MS Access or the MS Access runtime if you don’t have MS Access installed. If you use the runtime, all you see is the main console where you can build forms and view data. Alternatively, if you have MS Access installed, you can view a limited number of MS Access tables, queries, and forms that make up the Pendragon Forms software. To use the techniques described in this post, you will need to have MS Access installed on your computer.
Prepare MS Access
1. The first step is to make a backup copy of the Pendragon Forms database. Go to Program Files and copy the Forms32k.mdb. Save that copy to a new directory on your desktop. You will be working with this copy rather than the version of forms32k that Pendragon uses.
2. Open the copy of forms32k from within MS Access.
3. Right click on the navigation bar on the left and select “Navigation Options.”
4. Check “Show Hidden Objects.” After you do this, you will be able to view and edit all of Pendragon’s architecture. This means that you should use caution so you don’t break it. But then again, you are using a copy, right? Right? (stop and go back to step 1 if not)
Export Tables
5. Open the “lookupEntry” table under the “Tables” category. It will be grayed out. This table contains every lookup list item (LookupDisplay) and value (LookupValue). LookupID is the name of the lookup list. In this example, I have a list called “Gender” with two items: Male “0″ and Female “1″. Close this table and export to Excel by right-clicking on it and choosing “export.”
6. Open the “question” table, also grayed out. This table has several unnecessary fields that can be eliminated later. The key fields are “form,” “question,” “popupOptions,” and “internalName.”
form: The form ID
question: The question text (e.g., In which state were you born?)
popupOptions: The name of the lookup list (e.g., state)
internalName: The variable name of the question
Close this table and export to Excel by right-clicking on it and choosing “export.”
Create Variable Labels
7. Open the question.xls file in MS Excel. Delete all of the unnecessary columns; keep “form,” “question,” “popupOptions,” and “internalName.”
8. Create a 5th column that is filled with a double quotation (“).
9. Create a 6th column called question80char. Type the following formula in cell E2. This formula extracts the first 80 characters of the question since STATA limits variable label length.
=MID(A2,1,80)
10. Create a 7th column called label. Type the following code in cell F2:
=CONCATENATE(“label variable “,D2,” “,E2,F2,E2)
See here for SPSS instructions.
Check out Part 3 for instructions on how to replace string values with numeric codes and create value labels.
