Quantifying Survey Responses

J

Jeff Schneider

A survey form was sent out that logs responses in a back end DB on a shared
server. Most questions were set up with fixed responses (yes/no, combo box,
checkboxes). Unfortunately, there are also some text boxes allowing
respondents to type freely.

I'm being asked to sift through these responses to identify any trends. In
addition to just reading through everything, I'd like to be able to somehow
group or count common responses. I'm not sure about the best way to do this
and am open to suggestions.

What I'd like to do is count how many times each word occurs accross all
records. For example:

RecordID Answer
1 "I would like to have a picnic"
2 "I would like play softball"
3 "I don't care. Maybe a picnic."

The result I'm imagining looks like this:

Word CountofWord
I 3
a 2
picnic 2
.... ...
softball 1

Just thinking about it makes my brain hurt, but I'd live to see how it's done!

Thanks in advance
 
D

Duane Hookom

You could create a table of key words. Add the table to a query with your
answers and set up a where clause

SELECT KeyWord, Count(*) as CountOfWord
FROM tblKeyWords, tblResponses
WHERE Instr(Answer,KeyWord)>0
GROUP BY KeyWord;
 
J

Jeff Schneider

My intention was to try to identify the keywords. Without seeing an
inventory, I would only be guessing as to what the keywords are.
 
D

Duane Hookom

If you don't want to review the data and type in a list of keywords then you
may need to write some code that cycles through every word in every record
and appends it to the keyword table unless it already exists in the keyword
table.

Then manually delete words like (a, the, and, to, for, I, would,....) and
create the query as I suggested.
 
J

Jeff Schneider

First, I must give my apologies to Duane - I didn't mean to sound
unappreciative for your suggestion. I read my previous reply again this
morning and I sounded like an ungrateful @$$.

I'm still fairly new to code, so I'm not sure if I can produce the real code
to match your psudocode, but you've certainly given me a good structure to
follow (learning opportunity!)

Thank you!
 
Top