record counting in the same group

[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...

 

[[MVP] S.Clark]

I would do this with VBA Code, not a query or multiple queries.

 

[Tom Ellison]

Dear Hans:

A query solution would be:

SELECT ID, Field,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Field = T.Field AND T1.ID < T.ID) AS Counter
FROM YourTable T
ORDER BY Field, ID

Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Elric]

I need something like this, but only counting the duplicates in the field and
adding three fields each with its own value of one when the value is present.
Field1
xxxx
xxxx
yyyy
zzzz
Fieldx Fieldy Fieldz
2 1 1
Thanks ahead! P.S. Sorry for the off reply. Need Help!