Lisa R. Holliday on Using Microsoft Access to Code Qualitative Data

I am Lisa R. Holliday, an Evaluation Associate at The Evaluation Group in Columbia, SC.  I was on an evaluation team that recently completed a needs assessment in a large rural school district.  On one survey, there were 3,277 student responses to two open-ended questions.

Initially, I planned to take a sample, rather than analyze all responses.  However, as I skimmed through the replies, I noticed there was a lot of repetition. Also, responses tended to be short with little elaboration or additional context.  This made creating a codebook easy, but made me wonder if there was a way to automate coding for certain responses, given the high amount of repetition.

Hot Tip: Microsoft Access can help in situations like this.

Access is relational database management software from Microsoft that works on Windows systems.  It is included as part of Office Professional or can be purchased separately. You can try it for free with a 30- day trial of Office 365 Home Premium.

Access lets you store, manipulate, and report data efficiently. Unlike Excel, Access can run queries that search for words that are “like” your target, which allows you to account for some variations in spelling.   Using the method described below, I was able to automatically code 70% of responses.

Cool Tricks:

Step 1: Create a new database in Access.  If you saved your data in Excel, you’ll need to import it into Access.  Right click on “Table 1,” select “Import” then “Excel.”  Select your data.

Step 2: From the ribbon, select “Create” then “Query Design.” Make sure the name of your table is highlighted in the “Show Table” box.  Select “Add,” then close the window.

Step 3: Right click in the Field box and select “Zoom.”

 

Step 4: In the “Zoom” field, enter the following query:

Column Results Name: IIf([Table Name]![Name of column you are analyzing)] Like ‘*Search term*’ , (Code from Codebook),0)

For example if I wanted to find all responses that mentioned “excel” or “word” in Column 19 of my data, my query would look like this:

Results6: IIf([5-29 Data]![19] Like ‘*excel*’ Or [5-29 Data]![19] Like ‘*word*’,6,0)

 

This tells Access to look at Column 19 in the table named “5-29 Data,” and identify words like “excel” and “word.”  The results appear in a new column named “Results6,” and matched responses will be coded as “6.”

Repeat steps 3-4 for each item in your codebook.

Step 5: Once you have entered all items from your codebook, select “Run” from the ribbon under the “Design” tab.

 

Step 6: To export your results to Excel, right click on the name of the query you ran, select “Export,” then “Excel.”

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.