Average multiple fields in a query

Question

getting error when I try to avg 4 fields in a query.  "Syntax error in query"

Expr1: (Avg([AD QTR 1]),([DR QTR 1]),([PD QTR 1]),([TD QTR 1]))

I tried adding + signs instead of the comma's but get a sum of fields instead of avg.

Answer 1

John's first example is the only one I am seeing with the brackets correct.  After the Nz was added, you were only dividing the last number by 4, not the entire result.  Is that the issue?

Answer 2

If you want to eliminate the nulls altogether then this --

IIF([AD Qtr 1] Is Null, 0, [AD Qtr 1]) + IIF([DR Qtr 1] Is Null, 0, [DR Qtr 1]) + IIF([PD Qtr 1] Is Null, 0, [PD Qtr 1]) + IIF([TD Qtr 1] Is Null, 0, [TD Qtr 1]))/(IIF([AD Qtr 1] Is Null, 0, 1) + IIF([DR Qtr 1] Is Null, 0, 1) + IIF([PD Qtr 1] Is Null, 0, 1) + IIF([TD Qtr 1] Is Null, 0, 1))

Answer 3

Not 'zeros' but NULLS --

Nz([AD Qtr 1], 0) + Nz([DR Qtr 1], 0) + Nz([PD Qtr 1], 0) + Nz([TD Qtr 1], 0)/4

Answer 4

I added the nz because i do have zero's but must be doing something wrong?

Expr1: nz([AD Qtr 1]+[DR Qtr 1]+[PD Qtr 1]+[TD Qtr 1])/4

Answer 5

The Avg function averages over multiple rows in a query, not over multiple fields. I'm guessing (hoping!) that these fields are from a Crosstab query rather than stored table fields - if they are the latter your table design is more like a spreadsheet than a table!

To get the average of these four fields, and assuming that none of them are NULL, just calculate the average directly:

Expr1: ([AD Qtr 1]+[DR Qtr 1]+[PD Qtr 1]+[TD Qtr 1])/4.

If some may be NULL you'll need to use the NZ() function to convert null to zero, and decide whether you want a null value to reduce the average or be treated as zero.