restricting 0.00% in AVG

[Guest]

I am trying to get an average of a percentage field, I do not want to average
in 0% records. When I run a select query using <>0 in the criteria it shows
the correct number of records. When I try to AVG the totals it includes those
records with 0%

Your help is appreciated.

 

[Guest]

First write a query that uses >0 (or <>0) to filter the data. Close and save
it. Then make a second query that uses the first and AVG that one.

 

[John Spencer]

The basic SQL statement would be one of the following

SELECT Avg(IIF(PercentField=0,Null,PercentField)) as MyAverage
FROM TABLE

OR

SELECT Avg(PercentField) as MyAverage
FROM TABLE
WHERE PerCentField <> 0


If you are using the query grid, add the Percentage field to the grid one
more time
Change Group By to Where
Put <> 0 in the criteria under this field.