Averaging Column Data

[Guest]

I have a database, tied to an .asp web page that collects the answers to 10
questions and qrites them to a table.

The answers to the questions are stored as numeric data in columns Q1..Q10
I am trying to get the average of the columns that are not blank in a query.

The table is indexed with an autogenerated surveyID

I was using ([Q1]+[Q2]...+[Q10])/10 but this does not take into account the
fields that are blank. I tried the average function but it would not seem to
let me do that.

Perhaps I didn't create the database tables correctly. But I wasn't sure how
to create a new record for each response to each question. from the asp page.

 

[John Spencer]

Build a union query to normalize your data and then you can use the SQL
Aggregate functions.

SELECT SurveyID, Q1 as Response, "q1" as ColTitle
FROM YourTable
UNION ALL
SELECT SurveyID, Q2, "q2" as ColTitle
FROM YourTable
UNION ALL
....
UNION ALL
SELECT SurveyID, Q10, "q10" as ColTitle
FROM YourTable

With that saved as a query (qUniResults). You can then use it to get your
averages by question, by surveyId, etc. You can also get counts of how many
times the question was answered, etc.

SELECT ColTitle, Avg(Response) as AvgOfResponse
FROM qUniResults
GROUP By ColTitle

OR

SELECT SurveyID, Avg(Response) as AvgOfResponse
FROM qUniResults
GROUP By SurveyID

OR

SELECT ColTitle, Count(Response) as NumQuestionsAnswered
FROM qUniResults
GROUP By ColTitle

OR

SELECT SurveyID, Sum(Response) as TotalOfResponses
FROM qUniResults
GROUP By SurveyID