Calculating Average Score

[bangsmic]

Goal: To take a 0-5 evaluation rating score for 12 instructor feedback questions and then divide that score by the number of questions (12 max) that had a rating of 1-5. The rating score of zero in this example indicates a N/A for the question. so...if one of the questions was scored n/a or 0 it should not be part of the calculation.

For example: 12 questions answered, all were a 5. Equals 60 points. 60/12=overall rating of 5 (on a 1-5 scale). 11 questions answered, all were a 5. 1 questions was rated n/a or 0. Equals 55 points. 55/11=overall rating of 5 (on a 1-5 scale).

Table structure: tblData
Fields include Q1, Q2, Q3, etc., which represent the score for each question in the record. Reviewing other posts, I suspect this may be my first problem. What is a better way to capture?

Problem: How to write the query to generate the answer outline in the goal above.

 

[dhookom]

You might want to look at the structure of At Your Survey. If you can't change your structure like this, you can create a normalizing union query that allows you to average results. Your tblData structure doesn't seem to have a primary key so I can't tell you the SQL of the query.

Duane
Hook'D on Access
MS Access MVP