Averages from multiple colums with ignored values

[Snak]

I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.

Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.

Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?

 

[neileg]

You have an un-normalised database. Instead of 40 fields in one record, you should have up to 40 records in a related table. Then your problem goes away, because:
a) You can use a criteria query to exclude the 0's
b) If there's no answer you won't have a record so you won't have any 9's

 

[Snak]

Hi - appreciate your reply, but...

The table contains other fields too - it actually records results from a questionnaire and contains references to other tables. Having a different table for each question would be normalisation gone mad. The table is part of a normalised database. I MUST record where a respondent has ignored a question (the 9's).

I realise that I may not have been clear in my original post, so let me try to clarify it. There are 120 records (1 for each respondant). Each record (respondant) contains the answers to 40+ questions. I need averages not for each respondant, but for each question, across all 120 respondants.

 

[Brianwarnock]

Logically no experience is a level of experience, therefore I submit that the 0 should be included in your averages, thus you only need to filter out the 9

Brian

 

[neileg]

Hi - appreciate your reply, but...

The table contains other fields too - it actually records results from a questionnaire and contains references to other tables. Having a different table for each question would be normalisation gone mad.

I didn't suggest a table for each question, I suggested a record for each answer, all in one table.

The table is part of a normalised database.

Only partly normalised.

I MUST record where a respondent has ignored a question (the 9's).

OK, so record it. Again you can filter this out in a query.

I realise that I may not have been clear in my original post, so let me try to clarify it. There are 120 records (1 for each respondant). Each record (respondant) contains the answers to 40+ questions. I need averages not for each respondant, but for each question, across all 120 respondants.

That doesn't change my advice. It just means you group by questionID instead of respondantID.

But at the end of the day, it's your database. If you want to keep the structure you have, you could build a query for each question and filter out the 0's and 9's to give the average you want.

 

[pranavnegandhi]

I agree with neileg. Your database schema could use some normalisation. By taking the simplified view that you present in your post, I can come up with the following -

RESPONDENT
respondent id name

QUESTION
question id question

ANSWER
question id respondent id answer

Populate the Answer table with your respondents answers for each question. Then run your aggregate functions on the answer column.

BTW, AVG(1 + 0) is 0.5 and not 1. I can't understand why you would want to interpret it as 1.

 

[Snak]

Hi Guys

Thanks for your responses. I'll have a play with your solutions.

Just to round things off:

The scores 1-5 are an evaluation of experience and you cannot evaluate someting you haven't experienced.

The average of 1 + 0 is 1 if you do not count the zero - these are averages of evaluations.

Thanks again.

 

[Brianwarnock]

It sounds to me that you are evaluating an experience, not experience. No I'm not nit picking the 2 are different, if you are evaluating people's experiece of say ACCESS then no experience is a valid value, if you are asking people to evaluate ACCESS then you can rule out the views of those with no experience.

Brian

 

[Snak]

Evaluation of experience

You're absolutely right. The questions our respondents are asked are to evaluate the usefulness (or not) of medical training activities. This allows us to guage the effectiveness of one trainer over another. Those respondents who did not attend/were not given the opportunity to experience the activity are not counted as contributing to the average evaluation across all respondents for each activity.

 

[Brianwarnock]

Its good when we agree

Cheers
Brian

 

[KKilfoil]

OP:
Search this forum on 'surveys' as this topic has been discussed before.

Rule of Thumb: If you ever find yourself wanting to add/average/count/std dev/etc with similar FIELDS in the SAME RECORD, there's a real strong possibility that your tables are not normalized.