By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Count IF help please

P: 35
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
Dec 12 '07 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, istya.

You may try to use SQL aggregate Count function.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) FROM ..... WHERE .....<>0;
  2.  
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.
Dec 12 '07 #2

P: 35
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
Dec 12 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT fldBuilding, Avg(fldParkingScore) FROM partc WHERE fldParkingScore<>0 GROUP BY fldBuilding;
  2.  
But, sure, this could be done for one aggregated field only.

Or you may use subqueries in calculations which allows aggregation on multiple fields.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT partc.fldBuilding, (SELECT Avg(partc_Alias.fldParkingScore) FROM partc_Alias WHERE partc.fldBuilding=partc_Alias.fldBuilding AND partc_Alias.fldParkingScore<>0);
  2.  
Dec 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.