Summarizing yes and no

[Joel]

TIA:

I have a table with yes/no fld.

I'm trying to generate a report to simply show "#_of_yes" OF
"#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want
to see the "no" data in the report.

Any ideas on approach?

I have tried report based on summay query but the "no" data is there.

Thanks,

Joel

 

[Jeff Boyce]

Joel

Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and
use the absolute value function to make sure it is a positive number -- that
gives you the number of yes'es.

Or you could create a totals query and count WHERE YourField = "True" (and
once more, with "False").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joel]

Thanks Jeff:

I misspoke..it is a text field with yes, no, and other, so the math approach
is moot.

I have tried a summary query with criteria for "yes or no" and counts of
each but the "no" data is now part of the report. I'm tring to only show the
yes and total data.

How would i use a summary query in a report and not show one of the groups?
Or do I need two different queries and then how to make one report with 2
queries (one for yes and one for total yes and no)?

Joel

 

[Joel]

Long way around but it works.

Summary queries for yes and no to get counts.

Then create report for each. On main report, add subreports and create text
box to add 2 together and textbox to calculate % and hide subreport for "no"
data.

Any eaiser way?

TIA,
Joel

 

[Duane Hookom]

To count the number of records where [UnknownField] contains "Yes" use a
control source of:
=Sum( Abs([UnknownField]="Yes"))
If you don't want to display records in your report with [UnknownField] =
"No" but you need them available for counting, you can use code to cancel the
printing of a record where the [UnknownField] ="No"
In the On Format event of the report section:
Cancel = ([UnknownField] = "no")

 

[John Spencer]

Simple query

SELECT Abs(Sum([SomeField] = "yes")) CountYes
, Count([SomeField]) as CountResponses
, Abs(Sum([SomeField] = "yes")) / Count([SomeField]) as ThePercent
FROM [Your Table]

In the query grid
-- Add Somefield to the query twice
-- Change first field to Abs([SomeField= "yes")
-- Select View: Totals from the menu
-- Change first group by to Sum
-- Change second group by to Count



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================