Quantifying Survey Responses

[Guest]

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

 

[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;

 

[Guest]

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

 

[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.

 

[Peter R. Fletcher]

Assuming that your dataset is not so large that you can scan through
it manually to choose your keywords, and/or you can specify the
keywords that interest you in some other way, Duane's approach, as
usual, will give you what you want. If you don't know what you want as
keywords, you may need to write some code to go through all your
records, extract all the individual words from the keyword field,
discard obviously unimportant ones ("I", "and", "the") and give you
the number of occurrences of each of the rest.

Pseudocode would look something like this:

tblWords:
PriKey - AutoNumber
Word - Text
Counter - Long Integer

tblNullWords:
PriKey - AutoNumber
Word - Text

For Each Record
For Each Word in Response
Find Word in tblNullWords
If Found Then Exit For

Find Word in tblWords
If Found
Increment Counter
Else
Add Word to Table
Initialise Counter
End If
Next Word
Next Record

Sorting tblWords alphabetically on Word would allow you to identify
most misspelled but (really) identical words. Sorting by Counter will
give you the common ones for tblKeyWords.

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

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

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!