counting "yes" answers in a query

[Guest]

i want to count the number of "yes" answers then get a percentage of them out
of all answers in that field.

 

[Jeff Boyce]

You didn't say if you have one Yes/No field per row (a well-normalized
design), or multiple Yes/No fields per row (a spreadsheetly design).

If the former, you can Sum() that field, then take the Abs() (absolute
value). Since Access treats a Yes as -1, the absolute value of the sum of
that field is the number of Yes answers.

To do a percentage, find out how many rows and do the division.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

Try:

SELECT Sum(Abs([That Field]))/Count([That Field]) as ThePct
FROM tblNoNameGiven;

 

[Guest]

If it is a Yes/No field, Yes is stored as -1. In a report you can use a text
box (say txtYesCount) with this calculation: =ABS(Sum([YesNoFieldName])) to
get the count of Yes values.
Then another box could have: =Format(txtYesCount/Count(*),"0.#%")