How do I calculate the percent of "Yes" in a Yes/No Access field?

[Guest]

Subjects responded "yes" or "no" and encoded in Access as a Yes/No field.
How can I get Access to calculate the percent of subjects who responded "yes"?

 

[Dirk Goldgar]

Subjects responded "yes" or "no" and encoded in Access as a Yes/No
field.
How can I get Access to calculate the percent of subjects who
responded "yes"?

In a query:

SELECT
Abs(Sum(YesNoField))/Count(*) AS PercentYes,
Abs(Sum(YesNoField=False))/Count(*) AS PercentNo
FROM YourTable;

You'd need to apply the "Percent" format to the output fields, as
they'll be returned as decimal fractions.

As calculated controls in a form header or footer, or a report
header/footer:

=Abs(Sum(YesNoField))/Count(*)
=Abs(Sum(YesNoField=False))/Count(*)

 

[David Cox]

-100 * avg(fieldyn)

In a query:

SELECT
Abs(Sum(YesNoField))/Count(*) AS PercentYes,
Abs(Sum(YesNoField=False))/Count(*) AS PercentNo
FROM YourTable;

You'd need to apply the "Percent" format to the output fields, as
they'll be returned as decimal fractions.

As calculated controls in a form header or footer, or a report
header/footer:

=Abs(Sum(YesNoField))/Count(*)
=Abs(Sum(YesNoField=False))/Count(*)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

-100 * avg(fieldyn)

Nice! That's a bit tidier. But I'd rather not rely on the assumption
that Boolean True is always stored as -1, even though that's true in Jet
databases. So I prefer to use the Abs() function, since most databases
I know of store True as either 1 or -1.

100 * Abs(Avg(fieldyn))

The factor 100, of course, converts the decimal fraction to a percent
value. However, it may be desirable to leave it as a fraction and just
format it as a percent, just in case this value is going to be used in
calculations of some sort.

 

[David Cox]

You are, of course, quite correct. I just wanted it to shine without all of
the ifs buts and be carefuls. (Its a weakness)

 

[Dirk Goldgar]

You are, of course, quite correct. I just wanted it to shine without
all of the ifs buts and be carefuls. (Its a weakness)

I understand completely. It's hard to write replies that get the job
done in the majority of cases, without burying the reader in lots of
details that are only sometimes relevant. No matter what you write,
someone is going to find something wrong with it. It goes with the
territory.