How to count last name letters

[jason]

Hi folks,
I would like to know how I can count how many records have the same letter.
For example, how many records start with the letter A, how many for letter
B, etc.

Seems simple enough, but I can't figure out how to work this.

Would appreciate some help.

Thanks.

 

[Dirk Goldgar]

In

Hi folks,
I would like to know how I can count how many records have the same
letter. For example, how many records start with the letter A, how
many for letter B, etc.

Seems simple enough, but I can't figure out how to work this.

Would appreciate some help.

Thanks.

The SQL for such a query would look something like this:

SELECT Left(YourFieldName, 1) As FirstLetter, Count(*) As NOccurs
FROM YourTable
GROUP BY Left(YourFieldName, 1);

Naturally, you'd have to replace "YourFieldName" and "YourTable" with
the actual names of the field and the table, and if they include spaces
or other non-standard characters, you have to enclose them in square
brackets ([]).

You could paste the above SQL, suitably amended, into the SQL View of a
new query. If you want to see how it would look if built in the visual
query designer, switch that query into Design View.

 

[Kerry]

SELECT Count(TABLE.ID) AS NumberRecords, Left([NAME],1) AS FirstLetter
FROM TABLE
GROUP BY Left([PRODUCT_NAME],1);

 

[jason]

Thanks to you both, that worked.