numbering records in a query

H

Hans

Hello all,
I need count records in a query for every type of Field in a table. Data
example:

ID Field
1 aaaaa
2 aaaaa
3 aaaaa
4 bbbbb
5 bbbbb
6 ccccc

and I need something like this:

ID Field Counter
1 aaaaa 1
2 aaaaa 2
3 aaaaa 3
4 bbbbb 1
5 bbbbb 2
6 ccccc 1

any idea ? Please help me......
Thanks in advance...
 
M

Michel Walsh

Hi,


SELECT a.ID, a.Field, COUNT(*)
FROM myTable as a INNER JOIN myTable As b
ON a.Field=b.Field AND a.ID>=b.ID
GROUP BY a.ID, a.Field



Hoping it may help,
Vanderghast, Access MVP
 
Top