query to do counts of yes's in yes/no boxes..

[Helen Martin]

I'm trying do some analysis on some data from a survey we did for a
non-profit I'm working with.. I'm really struggling with the data for
one of our questions..

we asked our respondents where they get their information about
community events and asked them to check in boxes to indicate

internet newpapers flyers posters television..

i have 188 responses to the survey..

we entered this data in the database as a series of y/n checkboxes..

now I can make a query which pulls these five fields and shows me all
the check boxes but I'm having trouble figuring out how to summarize the
data..

if I put -1 in the criteria to try to just extract the check marks, I
can only do 1 field at a time... (or say not 0)

even if I do that I can't get it to count the records after I enter the
criteria.. It will happily count up to 188 with no criteria, but as
soon as I try to sort out the yes's, it doens't work ..

can someone suggest how I can count the "yes's"..

also, I'd like to count them all in one query, so that I can then use
the results to create a bar chart... to show which ways people like to
get their information the best..

with thanks
Helen

 

[John Spencer]

You can use a calculated field

SELECT Sum(Abs([Internet])) as InternetYes
, Sum(Abs([Newspapers])) as NewsPaperYes
....
FROM YourSurveyTable

In the query grid
-- Add the five fields to the grid
-- Select View: Totals from the menu
-- Change Group by to Sum under each field
-- Change each field to read something like
InterNetYes: Abs([Internet])
-- Run the query

Summing a yes/no field words because the values are stored as -1 for a check
and 0 for no check. If you were doing this for another field type you would
need use a different technique.

By the way, if you do many surveys you might want to take a look at Duane
Hookom's sample survey database at

http://rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

This fully functional application uses a small collection of tables,
queries, forms, reports, and code to manage multiple surveys. Users can
create a survey, define questions, enter pre-defined answers, limit to list,
report results, create crosstabs, and other features without changing the
design of any objects.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..