Table structure for tracking survey results (MS Access)

Factual Questions
1

Hi folks. Looking for some general direction here.

I have been put in charge of coordinating the tracking of the results of a survey. The survey has ten questions, some with yes/ no/ don’t know, some with a list of more specific choices and an ‘other’ category. Each survey has a respondent ID. We’ll need to be able to present the total responses for each question by their answer, and calculate the percentages thereof. We expect to tally several thousand returned surveys.

So far I have thought of three possible ways to get this done, but am not sure which, if any, is the most efficient.

Do I create a large table with fields for each possible response? That is, a yes/ no box to count each answer. Seems awfully cumbersome that way. If I do, what do I use to calculate the results? Will one query do it or will I have to have one query for each question? If so, how do I unite the queries for a general report?

Do I create on field for each question, and fill it with a list box? Sounds good, but when people are keying in individual responses, it takes a bit of extra time to pull down the list box. Can I do it with check boxes? Same question with calculating the responses here. Also, how do I handle questions with more than one response?

Or do I create one table to hold the name/ id of the respondent, and ten related tables, one for each question? Again, there is the perennial question of once the answers are in, how do I calculate the totals.

Or am I going about this completely wrong? Any suggestions?

Thanks for reading this far. Any help will be very much appreciated.

2

OK, my solution (there are probably better ones):

Table1: Field Names are Question1, Question2, Question3 (etc.), with data type number (integer)
Form1: Create a form based on the table, where you have 3 option groups. I would just use the toolbox and insert the option group object, one at a time, which will run you through the option group wizard. You want 4 options per question (in the sample), and the description for each will be the answer for each

A Cat
B Dog
C Bird
D Fish

etc.
Set it for no default, and put the question itself in the option group header.
Query1: Let’s assume there are 4 answers for each question. Create 4 fields per question, similar to this:

1a: iif(question1=1,1,0)
1b: iif(question1=2,1,0)
…
2a: iif(question2=1,1,0)
etc.

Running the query, you’ll just get all the answers strung across the page, showing a “1” if the person answered that, or “0” if not.

Query2: Do a query based on Query1, and create summaries on each of the 12 fields.

You wind up with a single row of answers showing the sum of each response.

Report1: If required, set up a report according to your needs, based on Query2. You could add features either to the report or the Query2 to sum the answers to each Question, if desired, so you have total responses to each question.

3

May I recommend this site:
Access World Forums
The folks on the Access World BB are very helpful and knowledgeable.

4

The following assumes that each respondent can have only one choice for each question. In that case I would probably go with one table and several look-up tables. Let’s call the main table tblResponses. The fields would be something like this (I’m just of making up the kinds of information you might be gathering):

tblResponses:
ResponseID AutoNumber
Registered Yes/No
EthnicityID Integer
PetID Integer
GunControl Yes/No
…

Then you will have a lookup table for those Integer fields:

tblEthnicities:
EthnicityID Integer
EthnicityName Text(50)

tblPets:
PetID Integer
PetName Text (50)

On the input form set up the Yes/No like this:

  • Use a CheckBox
  • Set the triple-state property to Yes. This will let you deal with the “I don’t know’s”

For the integer fields you can go with ComboBoxes, ListBoxes or OptionGroups. I understand that speed of input is a concern, so remember that the power user will probably be using the keyboard to get around. It is not necessary to actually pull down the list for ComboBoxes, you just have to type a the first one or two characters (just enough to make it unique) and hit tab.

You mention that there are questions with multiple responses. Are you saying that, for example, if you’re asking about pets, the respondent might say, “Yep, I have a cat and a dog, and a canary.” If that’s the case the design will be a little more complicated.

One big caveat here is that database tools like Access are not very good at doing statistical analysis. I understand there are statistical packages out there designed speifically for Access that will make the analysis much, much easier.

Bill Norton
Austin, TX

5

I’ll try to give you the “generic” setup, but you might not like it. :wink:

First, let me state that you are entirely correct to spend a great deal of energy trying to design the tables. Most of the “bad” database applications are due to poor table design, often resulting in corrupt data. The reason I would advise against the solutions offered this far is they require a change in table design to accommodate changes in survey design. A more flexible design will allow you to change the survey and only require that you add, delete or modify records from the database.

My recommendation involves the use of 4 tables:

Respondents
Questions
PossibleResponses
SurveyResults

Respondents:

RespID - primary key
Name
(any other fields for data you intend to gather about the Respondents)

Questions

QuesID - primary key
QuestionNumber
QuestionText

PossibleResponses

PossID - primary key
forQuesID - foreign key to QuesID in Questions table
PossResponse - one entry for each possible response

SurveyResults

SurvID - primary key
forRespondentID - foreign key to RespID in Respondents table
forQuesID - foreign key to QuesID in Questions table
forPossID - foreign key to PossID in Possible Responses table

Using this structure, you can design any survey you like, assuming all responses must come from a list of all possibilities, with no “free-form” or essay choices. This makes the design of your input form a little harder, but you gain a lot in terms of database flexibility.

6

hardcore, that approach makes sense, and I’ve even tried it before. I eventually abandoned it because, for me, the ratio of added flexibility to added complexity (sort of the bang for the buck ratio) wasn’t worth it. I suspect simplicity of design will be a high priority for Rhythmdvl’s application.

Of course if I were developing applications for a polling firm I might go for the extra effort.

Bill Norton
Austin, TX

7

bnorton, I completely agree. It all boils down to the expected service life of the database, and the proficiency level of the developer. Though I might add, several databases I’ve started as a “throwaway” application have ended up as a noose around my neck until I redesigned them. :eek: