TINA - counting a value list

[Tina Marie]

Hi .. I want to do a count on a field that is a combo box value list ... yes
or no ... count up the 'yes''s?? Any suggestions

 

[Arvin Meyer MVP]

Assuming the yes or no is being saved to a table, open a query and add the
field, no change the query to an aggregate query (Totals query, use the
Sigma button) and change Group By to Sum. Since Yes is -1, the total number
of yes's will be the positive of whatever the result is.

 

[Tina Marie]

Hi Arvin ... its not a field type yes/no ... its a combo box drop down with
value list choices 'yes'/'no' ... they wanted the words, not the checkmarks
.... so count will not work (counts everything) ... going to try the abs(sum)
function .. let me know if you know another way ...
--
Thanks!!

T. Marie

Assuming the yes or no is being saved to a table, open a query and add the
field, no change the query to an aggregate query (Totals query, use the
Sigma button) and change Group By to Sum. Since Yes is -1, the total number
of yes's will be the positive of whatever the result is.

 

[Douglas J. Steele]

Recognize that how something's displayed to the user should not dictate
what's stored. It's not very difficult to show them Yes or No, but store -1
and 0 in the table.

If Arvin's suggestion doesn't work, you can use:

SELECT Count(*)
FROM MyTable
WHERE MyField = "Yes"

or

SELECT Sum(IIf([MyField] = "Yes", 1, 0))
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Arvin ... its not a field type yes/no ... its a combo box drop down
with
value list choices 'yes'/'no' ... they wanted the words, not the
checkmarks
... so count will not work (counts everything) ... going to try the
abs(sum)
function .. let me know if you know another way ...

 

[Tina Marie]

Hi Doug ... actually my way did work ... what I did was in my query, I used
the iif to say if yes, then -1 else 0 and put the abs function around it ...
then in my report I did an sum and it added up the 1's which were the yes's
.... good!! Thanks all ...
--
Thanks!!

T. Marie

Recognize that how something's displayed to the user should not dictate
what's stored. It's not very difficult to show them Yes or No, but store -1
and 0 in the table.

If Arvin's suggestion doesn't work, you can use:

SELECT Count(*)
FROM MyTable
WHERE MyField = "Yes"

or

SELECT Sum(IIf([MyField] = "Yes", 1, 0))
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Arvin ... its not a field type yes/no ... its a combo box drop down
with
value list choices 'yes'/'no' ... they wanted the words, not the
checkmarks
... so count will not work (counts everything) ... going to try the
abs(sum)
function .. let me know if you know another way ...