Loop through table, Count occurrences, send to report

[carolyns]

I am working with Access 2000, on Windows XP (all updates).

I designed a survey in Word with 150+ questions. Roughly 60 questions use a lookup list of 7 different responses.
I have the survey broken up into sections with each question numbered. As a test, I created a Survey database and chose one section of 10 questions and created a table that looks like this:

ID Q1 Q2 Q3...Q10
1 3 4 7... 2

ID will link to personal info, and other question tables.

I used a lookup table for the 7 possible responses.

I want to loop through my table working through each question so that I can count the number of times a particular response is made. Then I want to calculate the percentage from the total number of participants for that question.

I would like to either output the data into another table or a report, (whichever is easier) sort of like this:

Q1 1 30 ?%
2 8 ?%
3 0 ?%
and so on through 7. I would need to do this for each question.

I was thinking of something like:

Do until EOF
Loop through each record, column by column
compare response with lookup list
Count (IIf([response}="1",0 ) (would a Switch work here for all 7 responses?)
Print this to table
percent=Count of particular response/total participants*10 0.
Print this to table.

Can anyone help? I am not sure if this should be a UDF or ???.

Thank you in advance, Carolyn

[nico5038]

I am working with Access 2000, on Windows XP (all updates).

I designed a survey in Word with 150+ questions. Roughly 60 questions use a lookup list of 7 different responses.
I have the survey broken up into sections with each question numbered. As a test, I created a Survey database and chose one section of 10 questions and created a table that looks like this:

ID Q1 Q2 Q3...Q10
1 3 4 7... 2

ID will link to personal info, and other question tables.

I used a lookup table for the 7 possible responses.

I want to loop through my table working through each question so that I can count the number of times a particular response is made. Then I want to calculate the percentage from the total number of participants for that question.

I would like to either output the data into another table or a report, (whichever is easier) sort of like this:

Q1 1 30 ?%
2 8 ?%
3 0 ?%
and so on through 7. I would need to do this for each question.

I was thinking of something like:

Do until EOF
Loop through each record, column by column
compare response with lookup list
Count (IIf([response}="1",0 ) (would a Switch work here for all 7 responses?)
Print this to table
percent=Count of particular response/total participants*10 0.
Print this to table.

Can anyone help? I am not sure if this should be a UDF or ???.

Thank you in advance, Carolyn

Hi Carolyn,

Your problem exists mainly because you did't "normalize" the table.
You should have created a table with QuestionID, QuestionText, QuestionAnswerT ype.
For the Answers you would need QuestionID, Answerer and Answer.

With these tables you'll be able to do the totalling by using GroupBy queries.

You have two options:
1) Mimic these tables by creating first a UNION query and base the needed GroupBy's on these
2) Use the above UNION queries to fill a redesign of your tables.

The UNION's would look like:

select ID, "1" as QuestionNo, Q1 from tblQuestions
UNION
select ID, "2" as QuestionNo, Q2 from tblQuestions
UNION
select ID, "3" as QuestionNo, Q3 from tblQuestions
UNION
select ID, "4" as QuestionNo, Q4 from tblQuestions
UNION
... etc. ...
 

Getting the idea ?

Nic;o)