Adding up grouped response values

[Batox]

I'm working with Access 2003 and the integrated (jet) database.

I have a questionnaire with 10 questions. Responses are values from 0 to 5, here's the answer table layout:
tResults
questionnaireID (the questionnaire instance table containing person ID and date)
questionID (1-10, index to question table)
response (numeric response value 0-5)

For the statistic, the questions are evaluated in groups, G1 consisting of Q1,Q4,Q8; G2 = Q2,Q3; G3=Q5,Q7,Q9,Q10 and G4= Q6. Now I need the sum of the answer values of each group.

Example: Assume the following answers: Q1 = 1; Q2 = 0; Q3 = 5; Q4 = 2; Q5 = 2; Q6 = 4; Q7 = 1; Q8 = 4; Q9 = 0; Q10 = 3
I need to calculate the following group results: G1 = 7; G2 = 5; G3 = 6; G4 = 4.

To get the G1 value, I think that I have to run an SQL statement like "SELECT response FROM tResults WHERE questionnaireID = <current questionnaire ID> AND (questionID = 1 OR questionID = 4 OR questionID = 8); and then add the results (I think there's a way to get the sum of the query result?). I have to buid similar statements for the other groups (or is there a way to create a "smart" catch-all statement?).

The problem I have is that I'm not sure how to run SQL when building a report, or can I do it in a query? How do I get the <current questionnaire ID> transferred from the user interface to the query (or report)? The user can't enter it directly, I'd rather have a mask for that?

 

[neileg]

I wouldn't hard code the grouping in your query. Add a GroupID to your question table. Include that in your query and themn make it a totals query - group by the GroupID and sum the responses.

 

[Batox]

Thanks neileg for the tip. By adding groupID I can now make a crosstable query which gives the totals, and I could make a second query with the count (number of answered questions in each group). But I also need the average (sum divided by count), that would be the third xquery...

The solution should be a pivot table, with the groups as columns and sum, count and average as rows. But to get that, I first need a "simple" query which contains all the data for the pivot table, and I'm not quite sure how to formulate that... ?

 

[just.a.guy]

This might work as a Quick and Dirty solution.
Don't know if ACCESS will complain about the lack of a "group by" class.

Create a new query.
Then switch to VIEW/ "SQL". paste in the code.

Hope it works.

=========================================


select
sum(iif( r.q# = 1
or r.q# = 4
or r.q# = 8
, r.ans
, 0
)) as g1
, sum(iif( r.q# = 2
or r.q# = 3
, r.ans
, 0
)) as g2
, sum(iif( r.q# = 5
or r.q# = 7
or r.q# = 9
or r.q# = 10
, r.ans
, 0
)) as g3
, sum(iif( r.q# = 6
, r.ans
, 0
)) as g4
from response_table as r

===========================================