How to use AVG function to only average those records with a value>0?

[dkmidi]

Hi,

Here is my scenario:

Type Detail Cost
Fruit Apples 1.50
Fruit Bananas 2.00
Fruit Oranges 0 or Null Value
Fruit Pears 1.75

So I have 4 records but only 3 have a value. In the query when I go AVG(myTable.[Fruit]) Grouped BY and in a second column have myTable.Detail Is Not Null or myTable.Detail <>0.

I get an average based on 1.50 + 2.00 + 1.75 but divided by 4 records even though the average should be based on dividing by 3.

Anyone know how I can get the average to only divide by the number of records that have a value?

Thanks!

Derek

 

[Aivars]

myTable.Detail Is Not Null and myTable.Detail <>0 and myTable.Cost Is Not Null and myTable.Cost <>0

Aivars

 

[cmmrfrds]

If you force the 0 to a null it won't get figured in the average. In SQL Server there is a nullif() function that would do it, but in Access it may be different.

Try it with an iif.

Select AVG(iif myTable.[Fruit]=0,NULL,myTable.[Fruit]),
otherfields
from table where etc.....

 

[dkmidi]

Thanks guys!

Hey cmmrfrds, your code snippet worked great! The only thing that wasn't needed was the SELECT:

Avg(IIf([test].[Cost]=0,Null,[test].[Cost]))

Derek