434,701 Members | 1,970 Online
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 Matteo Apr 3 '12 #1
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 =Sum(IIf([X],1,0)) =-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 SELECT DISTINCTROW  Count(Estrazione_mese.Trasporto) AS ConteggioDiTrasporto,  Count(Estrazione_mese.Banchina) AS ConteggioDiBanchina,  Count(Estrazione_mese.Attesa_Ritiro) AS ConteggioDiAttesa_Ritiro,  Count(Estrazione_mese.Anagrafica) AS ConteggioDiAnagrafica,  Count(Estrazione_mese.Efficacia) AS ConteggioDiEfficacia,  Count(Estrazione_mese.Stalla) AS ConteggioDiStalla,  Count(Estrazione_mese.Raccolta) AS ConteggioDiRaccolta,  Count(Estrazione_mese.Altro) AS ConteggioDiAltro,  Sum(Estrazione_mese.Campioni_annullati) AS SommaDiCampioni_annullati,  Sum(Estrazione_mese.Num_frigo) AS SommaDiNum_frigo,  Sum(Estrazione_mese.Num_cisterne) AS SommaDiNum_cisterne,  Sum(Estrazione_mese.Num_az_agricole) AS SommaDiNum_az_agricole,  Sum(Estrazione_mese.Attestati_Addetto) AS SommaDiAttestati_Addetto     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 -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 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