How to count last name letters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jason - 15 Jun 2007 17:05 GMT

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.

Reply to this Message

Dirk Goldgar - 15 Jun 2007 17:11 GMT

> Hi folks,
> I would like to know how I can count how many records have the same
[quoted text clipped - 6 lines]
>
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Kerry - 15 Jun 2007 17:15 GMT

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

On Jun 15, 12:05 pm, "jason" <jasonsantos-
NOSP...@allstatesmedical.com> wrote:

> Hi folks,
> I would like to know how I can count how many records have the same letter.
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

jason - 15 Jun 2007 21:24 GMT

Thanks to you both, that worked.

> Hi folks,
> I would like to know how I can count how many records have the same
[quoted text clipped - 7 lines]
>
> Thanks.

Reply to this Message