Averaging and not counting Zero's

[Guest]

Is there a way to average a group of numbers in a query and not include the
0. I know I could say Not 0 in the critieria, but I need the other fields to
show up regardless of this one particular record as a zero

 

[Michel Walsh]

SELECT ..., AVG(iif(fieldName =0, null, fieldName) ), ...
FROM somewhere
GROUP BY ...



Note that the 'other' fields would have to either be aggregated (MAX, MIN,
FIRST, LAST, .... ) either part of the GROUP BY list.

Changing the zero for a null would make the value to be discarded by AVG (as
it also occurs for any aggregate over a field name).

Vanderghast, Access MVP