Average (1 Viewer)

[mrabrams2]

I would like to figure the average without including the zeros.

REC#      MyCOUNT
1               0
2               5
3               12
4               0
5               3
6               5
7               16

I'm not a great mathematician, so using a calculator, the Average for the above would be 41 divided by 7 = 5.857...
The SQL is :
Avg(tbl_ABC.MyCount) AS AvgOfMyCount

What syntax do I use if I want my result to ignore the zeros?
41 divided by 5 = 8.2

Your help is always appreciated.
Michael

 

[jfgambit]

Create two queries:

In the first pull in the field and set the Criteria to <>0

Have the second one feed from the first, chage it to a Grouped by Query by hitting the Sum button "that funy looking E on the toolbar" And change the Grouped By to Avg

Should return the results you need.

 

[mrabrams2]

Works great - Thanks !!

Michael