Problem aggregating and summarising 2 tables 1

[shteev]

I'm writing a database in MS Access 2000.

I have 3 tables:

1. Questionnaire Subjects

2. Type A Questionnaires
- Subject (from table 1)
- Average Score
- .... <additional fields>

3. Type B Questionnaires
- Subject (from table 1)
- Average Score
- .... <additional fields>

Type A and Type B questionnaires differ from each other in their additional fields.

I've constructed a query to give an overall average by subject, for both types of questionnaire:

SELECT DISTINCTROW Subjects.Ref#, Avg([Ques Type A].Average) AS A-Avg, Avg([Ques Type B].Average) AS B-Avg

FROM ([Ques Type B] INNER JOIN Subjects ON [Ques Type B].Ref# = Subjects.Ref#) INNER JOIN [Ques Type A] ON Subjects.Ref# = [Ques Type A].Ref#

GROUP BY Subjects.Ref#;


This works fine. I now want to combine the averages for Type A and Type B questionnaires, for each subject. But if I add any of the following columns:

Count([Ques Type A].Average)
Count([Ques Type B].Average)
Sum([Ques Type A].Average)
Sum([Ques Type B].Average)

This works fine. I now want to combine the averages for type A and Type B questionnaires, for each subject. But if I add any of the following columns:

Count([Ques Type A].Average)
Count([Ques Type B].Average)
Sum([Ques Type A].Average)
Sum([Ques Type B].Average)

the figures produced are wrong, and I can't figure out how they're being generated. Help!

 

[rac2]

combine the averages for type A and Type B questionnaires"

What do you mean by combine?

Are Subjects topics, rats, or people? <-Humor.

It is a bit odd to have a column named AverageScore because I usually think of that as something to be calculated. Do you mean to store the result of a calculation as fixed data in your tables? Are these tables actually queries with expressions named AverageScore?

The FROM clause with JOINs in your query is like a table. It might have one row for a subject or it might have many rows. It will have multiple rows for a subject who has more than one row in any table. Probably a subject has one row in the Subjects table. Suppose some subject has 2 rows in Table A and 3 rows in Table B. Your query will then have 6 rows for that subject.

The Type A average will be in 3 rows, one for each of their rows in the Type B table.

So when you COUNT(Ques Type A].Average) you might get 6 when you expected 2.

 

[shteev]

You're right, these tables are actually queries with expressions named AverageScore. I left this detail out to simplify my question because I didn't think it relevant. Is it?

Given what you've told me, I'm concerned that my query may not even be calculating averages for Type A and Type B correctly. Is it?

By 'combining' the two Types for an overall average, I mean adding together the average scores for all of both type of questionnaire, and dividing them by the total number of both types of questionnaire, producing a mean. How can I achieve this?

Thanks for your help.

 

[rac2]

The fact that Average is a VIEW of an underlying table of detail means that you can define another VIEW using both underlying tables which combines data with a UNION. (As you probably know, a VIEW is a SQL concept represented in Access as a saved query.)

Suppose you have averages for each subject on questionnaires A and B obtained like so.

SELECT RefNumber, AVG(response) AS AvgA
FROM QuestA
GROUP BY RefNumber

SELECT RefNumber, AVG(response) AS AvgB
FROM QuestB
GROUP BY RefNumber

My queries would be used with tables that have one row for each subject and questionnaire item; the subject is identified by RefNumber; and the score on an item is in the response column. A subject completed one questionnaire, no repeated measures. 100 subjects completing a 10 item questionnaire would have 1000 rows in one table for that questionnaire.

The average for a subject on both questonnaires could be obtained this way.

SELECT RefNumber, AVG(response)
FROM (
      SELECT RefNumber, response
      FROM QuestA
      UNION
      SELECT RefNumber, response
      FROM QuestA
)
GROUP BY RefNumber

This gives equal weight to each item. Therefore the longer questionnaire will contribute more to the average for a subject.

If you wish to give the two questionnaires equal weight then this is the query.

SELECT RefNumber, AVG(AvgQ)
FROM (
      SELECT RefNumber, AvgA AS AvgQ
      FROM AvgQuestA
      UNION
      SELECT RefNumber, AvgB
      FROM AvgQuestB
     )
GROUP BY RefNumber

Here I have referred to the first two queries by the names AvgQuestA and AvgQuestB.