Embeding an average with a conditional (help)?

[callhandler]

Simplifying my problem:

I have a database with a table with the following fields:

Date, Q1, Q2, Q3

Date = the date
Q1= answer to a question, either value 1 to 5, or 9 for No Answer
Q2, Q3= similar to Q1

I want a query that will return for a given date the average response to the question [bold]EXCLUDING[/bold] the 9 answers (ie. the not applicabale values).

The complication is that, for example, there can be an answer to Q1, Q3 but no answer to Q2.

I am new to SQL so any advice will be helpful.

 

[PaulBricker]

The setup you are using isn't normalized and that's what makes you solution harder. The normalized version would look like this:

Date      QuestionNumber     Response

Anyway, that aside to do what you want, you will need something like this for each question
IIf(QNumber <= 5,1,0)
Add one of these for each question. Then turn on the Totals, and Group By Date, set the Total line for the Question fields to Sum and set the Total line for your IIf expressions to Expression. Then in a new column of your query put this expression (changing the names to what's appropriate)

(SumofQ1 + SumOfQ2 + SumOfQ3...SumOfQn)/(Expr1 + Expr2....+ Exprn)

That should give you the average for you question without including the values for No Answer.  

Paul

 

[callhandler]

Your solution works.

Even so, ... I think before I get in too much trouble I'll work a way to normalize the data ... thanks for the advice