Creating a frequency table 1

[Hoving]

I am trying to create a report to show a frequency table
of survey data.

The data are from 16 survey questions which are answered
on a 5-point Likert scale (e.g., 1 = Strongly Agree, 2 =
Agree . . . 5 = Strongly Disagree).

The data entry form uses option groups (i.e., check boxes) for each survey question, so the user checks the box (e.g., Strongly Agree, Agree, etc.) that corresponds to the answer on the actual survey. The answer for each survey item is stored as a number in the table. So the table of data looks like this:

subjectid Q1 Q2 Q3 . . . Q16
---------------------------------
10962 1 3 2 2
10545 2 2 1 4
15463 2 3 1 5

. . . where 1 = Strongly Agree, 2 = Agree, etc.

I'd like to create a report that aggregates the survey answers into a frequency table. For instance, for each survey question, I would like to show:

a) the number (i.e., count) of people who answered
Strongly Agree, the number of people who answered Agree, etc.
b) the total number of people who answered the question
c) the average/mean answer (e.g., an average answer of
1.5 would show that, on average, people Strongly Agreed
to Agreed with the statement)

The table/report might look like:

SA AG DG SD
Item 1 2 3 4 5 Totals Mean
---------------------------------------------------
Clean 5 3 1 2 0 11 ?
Nice 4 2 5 0 1 12 ?
Tasty 3 1 2 4 2 12 ?
.. . .
---------------------------------------------------

The above table shows that 5 people responded Strongly Agree to "Clean," 3 people said "Agree," and so forth. A total of 11 people answered the "Clean" question.

It would be even better if the table could also show
percents, as in:

SD AG DG SG
Item 1 2 3 4 5 Totals Mean
----------------------------------------------------
Clean 5 3 1 2 0 11 ?
45% 27% 9% 18% 0% 100%
Nice 4 2 5 0 1 12 ?
33% 17% 42% 0% 8% 100%
Tasty 3 1 2 4 2 12 ?
25% 8% 17% 33% 17% 100%
.. . .
----------------------------------------------------

The data is in the table, but I can't figure out how to
write the query and report so that the data is caculated
and presented to create a frequency table like the one
above. Any ideas on how to proceed? I don't think a crosstab query would work, because there isn't a field called "Survey Question" which lists all of the questions (which would constitute the X axis). Perhaps I should restructure the table and form? Any ideas?

Thank you.

Kurt

 

[dhookom]

Your data isn't quite normalized so you are having issues. The first step would be to normalize your data using a UNION query.
==quniSurvey============
SELECT SubjectID, "Q1" as Qst, [Q1] as Answer
FROM tblSurvey
UNION ALL
SELECT SubjectID, "Q2", [Q2]
FROM tblSurvey
UNION ALL
SELECT SubjectID, "Q3", [Q3]
FROM tblSurvey
UNION ALL
...etc...

You can then create a crosstab query based on quniSurvey. The crosstab would have [Qst] as the Row Heading, "A" & [Answer] as the Column Heading, and Count(SubjectID) as the Value.

You can then create a report with totals and percentages.

There is a complete, normalized survey (At Your Survey) available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp

Duane
MS Access MVP

 

[Hoving]

Duane:

Excellent feedback, and the sample (At Your Survey) database is outstanding. It's just the kind of normalized model I'm looking for.

Thanks.

Kurt

 

[Hoving]

I spoke a bit too soon. I'm having a problem with the At Your Survey database (and my derivative of it) that I can't figure out.

In the Survey Response Entry form, when I select a survey (e.g., "Sample&quot, enter a name, and click the "Enter Results" button, the appropriate form appears and I can enter answers. However, if I click the "New Response" button, I can select a survey and enter another name, but when I click "Enter Results" the database freezes and shuts down. I get the "Microsoft Access has encountered a problem and needs to close. . . " message.

Any idea what's causing this?

If references have anything to do with it, here's what I have checked:

- Visual Basic For Applications
- MA 10.0 Object Library
- MA DAO 3.6 Object Library
- OLE Automation

Thanks.

Kurt

 

[dhookom]

I'm not sure what your issue could be. I originally wrote AYS in Access 97 with DAO 3.5. You might want to create a new, blank mdb and then import all the objects and then try again. Make sure you have a reference set to DAO.

Duane
MS Access MVP