Group and count in a query

[Fred]

I'm boiling this down so that the answer will be small enough for my brain to
understand.

Let's say that I have table named "People" that has has a field "FirstName",
many of which are duplicated.

I would like a query to puts out a row for each first name. The first
column is the FirstName and the second column is the number of people who
have that first name. So, if there are 10 records in the table, with 6
Johns and 4 Dougs, the query would put out only 2 rows as follows:

John 6
Doug 4

Thanks in advance

Fred

 

[Jeff Boyce]

Fred

Take a look at a "Totals" query.

Open a new query in design view.

Add the [People] table.

Add the [FirstName] field and, say, the rowID field (well-normalized
relational database tables have primary key fields - use that one).

Now click on the Totals button (the sigma character, looks like a sideways
"M").

Use "GroupBy" for [FirstName] and "Count" for the other field.

Run it!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

Thanks!

It worked and was also very educational.

Fred

 

[KARL DEWEY]

I do believe that you will get the same results with or without the ORDER BY
unless you use ORDER BY DESC.