Count and sort queries - please help!

[Guest]

Hi, i'm relatively new to Access to will just have to describe this problem
in my words - hopefully someone can help!

i'm dealing with a recruitment database for a study. in one of the forms on
the database there is a drop down box which displays the site the person was
recruited at (8 selections). across other parts of the same form there are
various criteria on which a person can be excluded or included from the
study. these criteria are selected again through drop down boxes which either
say 'yes' or 'no' depending on whether this criteria applies or not. the
'site' field stores in the table as a number 1-8 and if 'yes' has been
selected to a criteria, this stores as '1' in the table.

i want to be able to run a query that displays how many people have '1' in a
given criteria and then sort this by site recruited. i.e. there is an 'out of
area' criteria so i want to know how many patients have '1' in this field
(were out of area) at each site. i tried the following query:

the first column had the 'Site' field and in the Totals row I selected
'Group By' and chose ascending.

The second column had the 'Out of Area' field and I used the 'Count'
function in the Totals column and entered the criteria as '1'

when i ran this query i got no error boxes but no results came up at all.
there is plenty of data in the tables to be able to give an answer so i've no
idea why it's not working.

Can anyone help?! cheers, Emma

 

[John Spencer]

The criteria as you have entered it would only return a record if the total
count was 1. What you want is to count a record if the value is 1.

Add your 'Out of Area' field to the query one more time
Set Total to WHERE
Set Criteria to 1

Remove the criteria under the 'Out of Area' field that you have set to 1

OR even simpler (no where clause needed and no criteria needed)
Remove the criteria under the existing Out of area field and use SUM
Field: [Out of Area]
Total: SUM
Criteria: <<Blank>>

By the way, if you wanted to get a Count for all the criteria fields that
are Yes (1) at one time you could use SUM instead of count
Add the Site Field (GROUP BY)
Add each of the "Criteria" fields (SUM)
(Do not add any WHERE fields - Sum will add the ones (and zeroes) for each
criteria field and give you a count.

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