Number & Subnumber 1

[georgesOne]

Hi everybody,

I would like to have a query which gives a result with # increments and sub# increments as follows (only Name is part of the underlying table)

# Sub# Name
1 1 Ali
2 2 Anton
3 1 Barton
4 2 Beaver
5 3 Bertha
6 1 Christine etc

No idea where to start, especially for the sub#.

Any hint is appreciated.

Kind regards, Georges

 

[PHV]

Typed, untested:

SELECT (SELECT Count(*) FROM tblName WHERE Name<=N.Name) AS [Number]
, (SELECT Count(*) FROM tblName WHERE Name<=N.Name AND Left(Name,1)=Left(N.Name,1)) AS SubNumber
, Name
FROM tblName AS N

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

As always... thanks a lot, PH.My query is somewhat more complicated (multiple joins) so I need to find out how to implement your suggestion.Anyway, very good start.Regards, Georges