Create query that sums up categorical data

[Liz Yetter]

Hi-
I need some help with Access 2003 queries. Please keep in mind that I
am NOT a programmer.

I have a table filled with gender (male or female), race (black,
white, asian, etc), and ethnicity (hispanic, not hispanic, unknown).
I need to find a way to do a query that will tell me the following:
1. how many males
2. how many females
3. how many white males, white females, black males, etc.
4. how many white hispanic males, how many white nonhispanic males,
how many black hispanic males, etc...
5. How many of 1,2,3,&4 between particular dates.

I'm not sure if I'm doing this right, but basically, I need the query
to spit out a number and not just line up all the males like it's been
doing.

Any help is appreciated.
Thanks!
Liz

 

[Duane Hookom]

I wouldn't even try to do this with a single query. You have lots of
individual simple queries that can return your results. If you want to
display all of these in a report, you can place multiple subreports based on
your individual queries.

 

[John Spencer]

You need to use a totals query to get the results you seek

For instance
SELECT Gender, Count(Gender) as CountGender
FROM YourTable
GROUP BY Gender

In the query grid, you would use
-- Create a new query with your table
-- Add the Gender field TWICE
-- Select View: Totals from the menu
-- Change the second GROUP BY to Count

If you want to do this for Gender and Race at the same time
-- Create a new query with your table
-- Add the Gender and the race field
-- Select View: Totals from the menu
-- In a blank Field "cell" enter
CountThem: Count(*)
-- Set the TOTAL "cell" to EXPRESSION


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