Averages? how to not include 0's?

[DJKAOS]

I'm trying to calculate the average of a column in a query but I dont want it to include the zero's in the calculation.

For example
COLUMN01
54.3
0.0
0.0
0.0
54.9
0.0
98.0

I want it to average only 54.3,54.9,98.0
I tried having my query return Null's instead of zero's because Avg ignores Null's...but then the Form doesn't like it and wont let me format the numbers.

Thanks for any help

 

[Rolliee]

Put a WHERE [nunfield] <> 0 )on the Query and so an average or the resulting recordset.

rollie@bwsys.net

 

[DJKAOS]

Thanks that wont work though I dont think.
for instance I have
COLUMN01 COLUMN02 COLUMN03
54 0 32
454 3 0
0 454 43
0 0 4

Where do I put a where clause?
I could see doing WHERE [COLUMN02]<>0 but then that would eliminate tons of records that I need...that happen to have column2 as 0 but all their other fiels may have a number in them.

 

[MichaelRed]

Then do the avg query on each col seperatly?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Rolliee]

use, for instance,

AllSum = dsum(&quot;[column1]&quot;, &quot;[MyTable]&quot;, [Fields(0) <> 0)
AllCount = dcount(&quot;[column1]&quot;, &quot;[MyTable]&quot;, [Fields(0) <> 0)

AveX = AllSum/AllCount

R

 

[stormcloudgathering]

To Rolliee
please would you help me?
I too have this bother of needing to have a report show the average only of positive values.
Your Allsum thingy looks like it would be what I need, please would you tell me where I need to write this.
Would it go in the query from which the report is written? or in the text box on the report itself?
Please forgive my ignorance...
many thanks
lynne

 

[MichaelRed]

Being a big (ANTI) fan of the Domain aggregates, I would suggest a layered approach. First query to replace all zeroes in the fields to be 'aggregated' with nulls. Second query to do the aggregates on the first query.

(dAvg, dSum, d*) are refered to as domain aggregates. I dislike them as they are really just 'wrapper' functions. The arguments which are necessary and useful to these are used to generate simple queries and the results are returned to variables. Particularly in the insrtance where multiple aggregates are desired from a single recordsource, the generation, compiliation and execution of multiple single queries will be much more expensive (execution time) than a few layered queries.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places