Aggregate Functions in Query

[Coral G via AccessMonster.com]

Hello!

I have a query that is gathering values from a field and then averaging those
values, which is working great except for one small snag. It is possible
that there is a 0 value in said field and I don't want to include that in my
average because it skews my average down (the field is a pricing field and
the 0 represent warrenty items that shouldn't be included in this average
price). When I put a >0 in the Criteria of the field it does not eliminate 0
values from the average but instead omits averages which total to 0. Is
there any way to get it to not use the 0 values when calculating my average?

Here is the criteria portion of the SQL statement right now: HAVING ((([Dispo
Translation Table].Scope)>"0") AND ((Avg([Cost Data].[Sale Tot]))>0) AND (
(Avg([CostLbr]+[CostMat]+[CostVendor]))>0));

Thank you for your help!

Coral

 

[Coral G via AccessMonster.com]

Of course... I had been banging my head about this for about two hours and as
soon as I posted this I figured out the problem. If you put the criteria in
the WHERE section iinstead of the HAVING section it will test before the
average is done

Hello!

I have a query that is gathering values from a field and then averaging those
values, which is working great except for one small snag. It is possible
that there is a 0 value in said field and I don't want to include that in my
average because it skews my average down (the field is a pricing field and
the 0 represent warrenty items that shouldn't be included in this average
price). When I put a >0 in the Criteria of the field it does not eliminate 0
values from the average but instead omits averages which total to 0. Is
there any way to get it to not use the 0 values when calculating my average?

Here is the criteria portion of the SQL statement right now: HAVING ((([Dispo
Translation Table].Scope)>"0") AND ((Avg([Cost Data].[Sale Tot]))>0) AND (
(Avg([CostLbr]+[CostMat]+[CostVendor]))>0));

Thank you for your help!

Coral