Count IF help please

[istya]

Hi,

I want to, if possible, create a query which counts the number of non zero entries in a table. I am aware that there is a DCOUNT in VBA, but atm I don't want to go down that route. Can anyone point me in the right direction please?

Thanks

[FishVal]

Hi, istya.

You may try to use SQL aggregate Count function.
[code=sql]
SELECT Count(*) FROM ..... WHERE .....<>0;
[/code]
Though a result of the query is not accessible in VBA but via recordset, if you've meant this. And the query cannot be used as controlsource.

Regards,
Fish.

[istya]

Thanks for that, but I don't think I'm explaining myself terribly well.

I have a table called 'partc' which holds about 20 numbers, i.e. scores on parking, toilets, lockers etc, per building. I want to workout the average of these score per building, but not include the zero values in the average.
ie
(sum of all scores / number of non zero scores) per building

[FishVal]

Well.

That is exactly Null values are for.
Null value is treated by aggregate functions as absence of value and averages, standard deviations etc. will be calculated as they are supposed to be.

If you, anyway want to proceed with zeroes you may filter the records containing nonzero values only.

[code=sql]
SELECT fldBuilding, Avg(fldParkingS core) FROM partc WHERE fldParkingScore <>0 GROUP BY fldBuilding;
[/code]
But, sure, this could be done for one aggregated field only.

Or you may use subqueries in calculations which allows aggregation on multiple fields.
[code=sql]
SELECT DISTINCT partc.fldBuildi ng, (SELECT Avg(partc_Alias .fldParkingScor e) FROM partc_Alias WHERE partc.fldBuildi ng=partc_Alias. fldBuilding AND partc_Alias.fld ParkingScore<>0 );
[/code]