Group and Count

[Cindy]

I have a query with a list of participants including their zip codes. I need
to group by zip code then count the number of rows per group. I'm not sure I
can even figure this out, since I can't seem to locate the "Group" button or
area. I have looked in all of my toolbars and have right clicked within the
query in the design view. Where else would it be located? Thank you.

 

[bcap]

The button is on the toolbar, it's got a Greek "sigma" on it (looks like a
bent capital "E").

You can also choose "Totals" from the View menu.

 

[Cindy]

OK, got the Group By to be in my design view but I'm confused as to how to
get it to "group" numbers. My database is something like this:

name state zip code
my table my table my table
Group By Group By Group By

I only want to group zip codes and then count the number in each group. Do
I have to build some kind of expression in here?

 

[John Spencer]

Add the ZIP CODE one more time to the query and change GROUP BY to COUNT.

If you don't want to count ZIP CODES for the combination or Name and Zip
Code, you need to remove the name field from the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Brendan]

How do you add a count function to a query for text (ie. business names,
etc). Do I have to create my own expression? Does someone have an example.

 

[John Spencer]

Count counts the presence of data in the field. It does not worry about
numeric value.

If you want to count the number of times a business name occurs in some
table.

Open a new query
Add your table
Add the BusinessName field TWICE
SELECT VIEW: Totals from the menu
Change GROUP BY to COUNT under one of the fields

That will list every business name and give you a count of the number of
times each business name appears in the table.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Brendan]

That is what I thought but my return in the column for each row shows the
number one

 

[Cindy]

I did that and now I have "1" in every row. It's not grouping the zip codes.
"Group By" is in every column on my design view as well. Do I have to tell
it what to "group by" or will it try it on it's own? Now it looks like this:

prt_first prt_last prt_ss_nbr p_hw_eligible prt_state prt_zip_code
prt_zip_code
Then the name of my table that I'm pulling info from is under each of these.
Group By is under each one except for the last zip code one which has "Count"

What did I miss? Thanks.

 

[John Spencer]

You are counting the records in the group. The group is defined by all
the group by fields. So, as a guess, your fields are defining a group
that contains exactly one record.

Perhaps you need to use a subquery to get the count.

SELECT prt_first, prt_last, prt_ss_nbr
, p_hw_eligible, prt_state, prt_zip_code
(SELECT Count(prt_zip_code)
FROM [YourTable] as Temp
WHERE Temp.prt_zip_Code = [YourTable].prt_Zip_Code) as ZipCount
FROM [YourTable]


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================