Count first character of string

[Vaporhead]

Good morning.

You guys/gals have been most helpful to me as I try and setup my database.

I am trying to count a field, but only count the first character of the values.

Example:

The values in my field are entered like:

3, (II,C)
3, (III,B)
5, (IV,C)
5, (IV,D)




I would like my query to count just the first character, in this example, '3' and '5', so the count return would be 2x '3' and 2x '5' without including anything after the first character.

Mark

[madpiet]

SELECT fc.FirstChar, COUNT(*)
FROM
( SELECT LEFT$([ColumnName],1) AS FirstChar FROM MyTable ) fc
GROUP BY fc.FirstChar;

[ArviLaanemets]

Something like

SELECT Left(YourField,1) As FirstChar, COUNT( Left(YourField,1)) As FirstCharCnt 
FROM YourTable 
GROUP BY Left(YourField,1)

[Vaporhead]

Perfecto! Thanks All!

[June7]

Don't think need to repeat Left() expression in Count(), just Count(*).

[Micron]

Val would be a lot simpler (assuming the field is never Null)?
EDIT - I meant vs Left or similar, just to get the first character, which appears to always be a number)

[June7]

Good point. What should happen if 'first character' could actually end up being more than 1 digit? Probably this data should be split into 3 fields.

[Micron]

Val reads the string from left to right and stops when it finds anything that does not resemble a number. So if that was 123, CXXIII you'd get 123. Should not be a problem then?

[June7]

Not if user really wants all digits to be considered. Assume that is the case until hear different. And as you indicated, errors on Null so may need Nz() as well.

Does not stop for period which is read as a decimal (or maybe a comma depending on regional settings).

Also assumes first character will always be numeric, as shown in example data.

Real solution is discrete data in own fields.

[Micron]

Does not stop for period which is read as a decimal

Good point Val(.456) returns 0.456
All depends on the data, but yes, discrete data is usually the best approach.

[ArviLaanemets]

In his/her opening post, OP clearly declared, that the count must be based on 1st character of entries in column - not the numeric part at left of entry, or not all left at dot there;
The examples of data given there had a number as leftmost character, but there was nowhere declared, that this applied to all data in column;
So using LEFT(YourField,1) was giving what OP asked for, whatever the entries in column were.

[Bulzie]

Vapor are the only values possible for that first character 0-9 (only 1 character in length?)?