By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 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

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


NeoPa
Expert Mod 15k+
P: 31,273
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
  1. SELECT DISTINCTROW 
  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
  15.  
  16.  
  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

NeoPa
Expert Mod 15k+
P: 31,273
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

NeoPa
Expert Mod 15k+
P: 31,273
Sweeda:
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.

Sweeda:
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.