Need Help To Summarize Survey Results

[Melanie]

I have an Excel worksheet that tabulates survey results for about 100 questions.
There are over 100 responses for each question. The responses vary. Some
questions are Agree, Neutral, Disagree, some questions are 1 to 5, some
questions are A to D and there are several more varieties.

I need to summarize the results for each question. For example, Q1: 35 Agree,
15 Neutral, 75 Disagree, Q2: 5-1, 12-2, 37-3, 55-4, 24-5, Q3: 99A, 0B, 2C, 4D,
etc for 100 questions.

The data is currently in Excel and if that is the best place to do the analysis,
I need suggestions on how to do it there. I am quite capable of linking to the
data from Access or importing the data into Access if Access is the better place
to do the analysis - I have already tried linking to test if there would be any
problems linking and it went smoothly. If Access is the place to do the
analysis, I need some help on how to do it.

Appreciate any and all help!

Melanie

 

[JMay]

Stay In Excel and check out the SumProduct() Function - It's the route to
take..
With your data as follows:
A B C
1 Q# Partic# Response
2 Q1 1 A
3 Q1 2 A
4 Q1 3 D
5 Q1 4 N
6 Q1 5 A
7 Q1 6 D
8 Q1 7 A

Your Output area (Table):

F G H
14 Q# Responses Count
15 Q1 A 4 << H15 contains
=SUMPRODUCT(($A$2:$A$22=F$15)*($C$2:$C$22=G15))

16 D 2 << H16 contains
=SUMPRODUCT(($A$2:$A$22=F$15)*($C$2:$C$22=G16))

17 N 1 << Etc

HTH

 

[Larry Daugherty]

As given,your application is not calculation intensive, it's data
intensive. There is very little analysis in your application that would
require the horsepower and risk and limitations of Excel. I believe you'd
be better to design a simple application in Access and thereby protect your
users and your data by not allowing direct interaction with your tables.
Note also that Access will provide for high accuracy and reliability by
providing alphabetized drop down lists of choices for answers.

As I see what you've given you'd need 3 or more tables:

Respondent (fields: there will be a primary (I'm a sucker for Autonumber
primary keys), Respondent ID even if you just roll your own serial number
(Do NOT use Autonumber for this one!) and a separate field for each
attribute you're tracking.. If you are gathering respondent specific data
and/or demographics they would be attributes in table Respondent.), The
last field should be an unrestricted field for Notes about this encounter.

Question (this table will include every question in your survey, fields: it
will have a primary key, the question text, notes about the question),

Answer (fields: a primary key, the answer, notes about the answer -- note
that table Answer will be a lookup table and it will have all of the
possible answers. I inferred from your prose that all possible answers
mapped to all possible questions. If that is NOT so then you may need a
junction table to associate the legal answers for each question - say table
QuestionAnswer.

The tables will hold your data but you now need to design forms for
entering the data and reports for displaying it. The forms and reports will
be based on queries on the various tables.

hth