How to calculate a monthly average (excluding certain months) in query

[neelsfer]

Its easy to calculate averages in Access queries using the Totals method, but what do you do in the following instance?

month-------quantity
1 ------------10
2-------------0
3-------------20
4-------------0
5-------------20

The normal way would be to add up and divide by the months
ie 50/5 = 10

My problem is that i want to EXCLUDE the months where you have a ZERO quantity.
then 50/3 = 16.67
Any suggestions on how to calculate this in a Query?

[ADezii]

Debug.Print FormatNumber((DSum("[Quantity]", "Table1", "[Quantity]>0")) / (DCount("[Month]", "Table1", "[Quantity]>0")), 2)

Result:16.67

Or:

SELECT FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2) AS Result
FROM Table1
GROUP BY FormatNumber((DSum("[Quantity]","Table1","[Quantity]>0"))/(DCount("[Month]","Table1","[Quantity]>0")),2);

Result:

Result
16.67

[neelsfer]

thx i will give it a try today

[neelsfer]

Mr Adezii, have you perhaps still got the file you used with the above formula? I would like to see how the code was used in the query. thx

[ADezii]

I just recreated the Query for you - here it goes.

[neelsfer]

thx i will try again to use it