creating a counter based on each id 1

[zevw]

I have 2 Employee IDs

and when querying the table I get for each individual ID a varied amount of records. I would like to count each record and restart my counter when I get to the next ID.

i.e.

ID # 654 has 7 records and ID # 876 has 4 records I want in my query results as follows

[tt] Counter ID# TxtFld
1 654 A
2 654 B
3 654 C
4 654 D
5 654 E
6 654 F
7 654 G
1 876 K
2 876 L
3 876 M
4 876 N[/tt]

 

[PHV]

You may try this (SQL code):
SELECT Count(*) AS Counter, A.[ID#], A.TxtFld
FROM yourTable AS A INNER JOIN yourTable AS B ON A.[ID#]=B.[ID#] AND A.TxtFld>=B.TxtFld
GROUP BY A.[ID#], A.TxtFld;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886