Query to extract data from survey data in a table (1 Viewer)

[ECLAIM]

I am currently working on a survey database.
The survey database consists of 4 tables, the main table is the survey table itself in which all responses from our clients are enterd.

Each staff member name can occur several times in this table. The table contain fields for holding response values from our client.

How do I generate a query that will break down each field value, as each field has up to 4 possible value which corresponds to the
response value from the client and base a report on the query e.g

q1 q2 q3 q4 q5 q6 q7 q8 q9
John Doe 1 yes no no da yes 3 yes no
Shane 3 no no yes da no 4 yes no
John Doe 4 no yes no yes da 3 yes no
John Doe 2 yes yes yes no yes 3 yes no
Shane 1 no no no da no 4 yes yes

The Idea then is to generate a report that tells what percentage of respondent entered 1 for q1 for John doe, and each of the field
in question. The percentage is based on how many survey forms were returned for John Doe. The same process affect Shane and any other staff member.

Could some one please help, if you will like to see a sample of the table, please e-mail me.

Thank you

 

[Pat Hartman]

Your table design does not lend itself to analysis via SQL since it is not normalized. You could of course write VBA code to do the calculations or you could restructure your data. To get it to a structure that is useable, either normalize it permanantly or do it on the fly with a union query containing as many select queries as there are questions (9 in your example). You need to end up with a recordset containing the following columns:

LastName
FirstName
ClientId
QuestionNum
Answer

Once the table has been restructured, the query to count the responses by value would be:

Query1:
Select LastName, FirstName, QuestionNum, Answer, Count(*) As AnsCount
From YourTable
Group by LastName, FirstName, QuestionNum, Answer;

Count the total number of responses:

Query2:
Select LastName, FirstName, QuestionNum, Count(*) As AnsCount
From YourTable
Group by LastName, FirstName, QuestionNum;

Figure the average:

Query3:
Select q1.LastName, q1.FirstName, q1.QuestionNum, q1.Answer, q1.AnsCount/q2.AnsCount As AvgAns
From Query1 as q1 Inner Join Query2 as q2 On q1.LastName = q2.LastName And q1.FirstName = q2.FirstName And q1.Question = q2.Question;