Counting the number of matching entries in a text field

[creolejazz]

Hi. Here's an easy one for you (that I can't seem to figure out).

I've got a table with 1,000 records. One text field is SCHOOL NAME. I want to count how many records there are of each school.

For instance, there may be several records that have Lincoln County High School, several more that are Lincoln County Middle School, and several more that are West Elementary School, etc. There may be 70 or 80 different schools out of these 1,000 records and I need the total number of each school.

How do I have Access count the number of records with a specific entry within one field? Thanks.

 

[pbaldy]

Try a query:

SELECT SchoolName, Count(SchoolName) AS HowMany
FROM TableName
GROUP BY SchoolName

 

[creolejazz]

Thanks for the reply. Sounds interesting. You lost me on "AS HowMany". I can't find a reference for that in the help file. Can you walk me through it a step at a time? Sorry for the hassle. Thanks.

 

[Brianwarnock]

AS HowMany
is naming the counted field

In the design grid you would put
HowMany:Count(SchoolName)

in a field

Brian

 

[pbaldy]

It's just an alias (a made-up name for the field). In the design grid of a query it would look like:

HowMany:

If you just copy the above into the SQL view of a new query and adjust the field/table names, it should work.

 

[creolejazz]

Sorry, guys. All this is doing is presenting me with the total amount of records. I have, no doubt, messed up something along the way.

I have placed this in the FIELD name in design view of my query:

HowMany: Count([SchoolName])

When that is the only field used it just counts the total number of records.

So I added a second field, SchoolName. Then it gives me this message: "You tried to execute a query that does not include the specified expression 'SchoolName' as part of an aggregate function."

Any help for this relative newbie is appreciated. Thanks.

 

[Brianwarnock]

Reread Pbaldy's SQL Post 2

Note the Groupby

Brian

 

[georgedwilkinson]

CJ, just create a new query in design mode, cancel the add table, and click on "SQL" in the upper left of the Access main window. In the screen that comes up, just copy in Paul's query. Then click on design or "View" to see the design or the data.