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

How do I count how many "Yes" are in a "Yes/No" field through a query?

P: 12
Hi everybody,
I'm trying to make a selection(?) query that counts how many "true"(or "Yes") are in several "Yes/No" fields. I've read something about a "dcount" function but I didn't completely get how to use it in the SQL code of the query. Could you please help me? Other solutions?
Many thanks

Apr 3 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,489
You include nothing of what you're working with so this must be very general.

Use Sum() and either tot up the times it's yes using IIf(), or simply negate the sum of the values on the understanding that the numerical equivalent of Yes is -1 :
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([X],1,0))
  2. =-Sum([X])
Apr 4 '12 #2

P: 12
Are you sure that is possible to SUM records of a "Yes/No" field? It doesn't look working..

Ok, I post some detail:

Expand|Select|Wrap|Line Numbers
  2. Count(Estrazione_mese.Trasporto) AS ConteggioDiTrasporto, 
  3. Count(Estrazione_mese.Banchina) AS ConteggioDiBanchina,
  4.  Count(Estrazione_mese.Attesa_Ritiro) AS ConteggioDiAttesa_Ritiro, 
  5. Count(Estrazione_mese.Anagrafica) AS ConteggioDiAnagrafica, 
  6. Count(Estrazione_mese.Efficacia) AS ConteggioDiEfficacia, 
  7. Count(Estrazione_mese.Stalla) AS ConteggioDiStalla,
  8.  Count(Estrazione_mese.Raccolta) AS ConteggioDiRaccolta,
  9.  Count(Estrazione_mese.Altro) AS ConteggioDiAltro, 
  10. Sum(Estrazione_mese.Campioni_annullati) AS SommaDiCampioni_annullati, 
  11. Sum(Estrazione_mese.Num_frigo) AS SommaDiNum_frigo, 
  12. Sum(Estrazione_mese.Num_cisterne) AS SommaDiNum_cisterne, 
  13. Sum(Estrazione_mese.Num_az_agricole) AS SommaDiNum_az_agricole, 
  14. Sum(Estrazione_mese.Attestati_Addetto) AS SommaDiAttestati_Addetto
  17. FROM Estrazione_mese;
"Trasporto", "banchina", etc. are "yes/no" fileds. I need to count only "Yes" and Estrazione_mese is a query.

Thanks again
Apr 4 '12 #3

Expert Mod 15k+
P: 31,489
Matteo, what you posted is not what I suggested. You are using Count() for the Yes/No fields (which won't work of course). You are now introducing the fact that you are working within SQL, which your question didn't mention. For performance reasons in SQL, I would try the second suggestion.

Your line #2 would look like :
Expand|Select|Wrap|Line Numbers
  1. -Sum(Estrazione_mese.Trasporto) AS ConteggioDiTrasporto,
Apr 4 '12 #4

P: 12
Thank you very much, it works, could you explain to me the "passages" of the calculation?
Without the "minus" it should give me something like: (-1) + (-1) + (-1) = -3
but it didn't, it gave me just another true or false.

What does -sum() mean?

Thank you so much, very kind as always
have a nice day
Apr 4 '12 #5

Expert Mod 15k+
P: 31,489
Without the "minus" it should give me something like: (-1) + (-1) + (-1) = -3
but it didn't, it gave me just another true or false.
I can't see what you describe, but your understanding of the calculation is perfect. The answer would be -3. If the field it's displayed in is configured to format that value as True/False or Yes/No, then that is another matter. It is only the format though, not the value. You were absolutely right in your expectation.

What does -sum() mean?
Return a value that is the negated equivalent of the Sum() value. In your example Sum() = -3 therefore -Sum() = 3.
Apr 4 '12 #6

Post your reply

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