coloumn trouble!

[Kate]

Hi there, have only recently started using Microsoft
Access, so i need all the help i can get.

I've set up a column with numbers running down i.e.

A13
E34
E35
A16

The problem is that each of these numbers has a letter
begining of which needs to be counted separately i.e. all
the a's.

Does anyone know how to do this? any help would be much
appreciated

Kate

 

[Ted Allen]

Hi Kate,

You can count the records by the first letter in the
field by using the left() function to grab only the first
letter, and then using a Totals query to group by the
letter and count the number of entries with that letter.

If using the query builder, just add the table to the
query and click the sum button on the toolbar (looks like
a greek sigma). Then, in the first column type the
Expression:

FirstLetter: Left([YourFieldName],1)

And in the next column add the field that you want to
count (probably the same field that you are extracting
the letter from). Whichever field you add, all records
will be counted unless they have a null value, which is
why you would probably want to use the field that you are
extracting the letter from.

Then, on the line that says "Total", choose "Group By"
under the first column (the left function), and choose
count under the other column.

The SQL for this query would look something like this, I
have used brackets for table and field names because I'm
not sure if yours have spaces.

SELECT Left([YourField],1) AS FirstLetter, Count
([YourTable].[YourField]) AS CountofLetter
FROM [YourTable]
GROUP BY Left([YourField],1);

Hope that helps.

-Ted Allen