query to count number of records for each letter of alphabet

T

tandemrider

I want to know how many records there are for each letter of the alphabet by
last name
 
T

Tom Wickerath

Hi Tandemrider,

Try the following query in the sample Northwind.mdb database. Create a new
query. Dismiss the Add Tables dialog without adding any tables. In query
design view, click on View > SQL View. You should see the word SELECT
highlighted. Copy the following SQL statement (Ctrl C) and paste it into the
SQL view (Ctrl V), replacing the SELECT keyword:

SELECT Left$([LastName],1) AS Letter,
Count(Left$([LastName],1)) AS [Number]
FROM Employees
GROUP BY Left$([LastName],1)
ORDER BY Left$([LastName],1);

You can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
T

Tom Ellison

Dear Tandem:

I'm thnking you may have a column where you are looking at the first
character in each value in that column. In that case:

SELECT LEFT(ColumnName, 1), COUNT(*) AS Ct
From YourTable
GROUP BY LEFT(ColumnName, 1)
ORDER BY LEFT(ColumnName, 1)

Is that perhaps what you want?

Change YourTable and ColumnName to the actual name of the table and column
you have.

Tom Ellison
 
Top