Condititional Average

[Guest]

Sorry for the blast of questions today. I would like to Average a field but
only for field where another field in that record equals a certin Value.
Here's the set up

Scores.B1 intake Method
Scores.B2

I would like to average scores.b2 but only for the records where Sscores.B1
Intake Method = 3

I con not resrict the qry to return only those records as I need all the
responses for the other fields. Is this possible? Thank you Jason

 

[John Vinson]

Sorry for the blast of questions today. I would like to Average a field but
only for field where another field in that record equals a certin Value.
Here's the set up

Scores.B1 intake Method
Scores.B2

I would like to average scores.b2 but only for the records where Sscores.B1
Intake Method = 3

I con not resrict the qry to return only those records as I need all the
responses for the other fields. Is this possible? Thank you Jason

If the query needs to include all records, the Avg() totals operator
won't work. Try instead an expression:

Sum(IIF([B1 Intake Method] = 3, [B2], 0)) / Sum(IIF([B1 Intake Method]
= 3, 1, 0))


John W. Vinson[MVP]

 

[Guest]

Thanks John that got me where I needed to be.