Exclude zero from average query

[falcondeer]

Hi all,
I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that’s 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that’s 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.
Any solution.
Thanks. ​

 

[pbaldy]

What is the SQL of the query? Make sure it's using a WHERE clause rather than a HAVING clause.

 

[falcondeer]

What is the SQL of the query? Make sure it's using a WHERE clause rather than a HAVING clause.

Hi

Honestly I used the query wizard, then I put "avg" insead of "Group By" in Total !

Should I write vba code instead and where.

Thanks.

 

[Galaxiom]

Add an extra column in the designer with another instance the field you want to exclude on the basis of its value. Change the Totals line to Where and enter your criteria.

The Where is applied first then the totals are calculated.

 

[Brianwarnock]

You do not need VBA, if you look at the SQL. View you will see the having clause change it to a where , note that it comes before Group By.

Alternatively in the design grid drag the column in a second time select Where from the Totals drop down and put the criteria under that column.

Brian

I see Galaxiom typed quicker than I

 

[Pat Hartman]

Perhaps you should rethink having the field default to 0. If the field has no default and so is null unless someone enters a value, then 0 has meaning because someone entered it and you probably want to include it in your average. When you have a default of 0 there is no way to tell if 0 is an actual value or no value was actually entered.

 

[falcondeer]

Perhaps you should rethink having the field default to 0. If the field has no default and so is null unless someone enters a value, then 0 has meaning because someone entered it and you probably want to include it in your average. When you have a default of 0 there is no way to tell if 0 is an actual value or no value was actually entered.

OMG, It is easy this way, thanks million.