Using Access 2010.
So if I had a table
COL1 | COL2
A | 1
B | 1
A | 2
C | 1
A | 3
Add a field to your table. Choose AutoNumber as its data type and make it the table's primary key. I named the field ID, so my version of your sample data looks like this ...
ID COL1 1 A 2 B 3 A 4 C 5 A
SELECT statement below returns this result set ...
ID COL1 COL2a COL2b 1 A 1 1 2 B 1 1 3 A 2 2 4 C 1 1 5 A 3 3
COL2a and COL2b show 2 methods to achieve the same result. DCount is Access-specific, and required quotes around the
m.COL1 text values. The second approach, COL2b, uses a correlated subquery so could work in a different database if you choose. And with that approach, you wouldn't need to bother about quoting text values.
Either approach basically requires the db engine run an additional query for each row of the result set. So, with a huge table, performance will be a concern. Indexing will help there. Add an index on
COL1 if there isn't one already.
ID already has an index since it's the primary key.
If you can't add a field, and the table doesn't already include another suitable field, then I think you're out of luck. You won't be able to get what you want with an Access query.
SELECT m.ID, m.COL1, DCount( "*", "MyTable", "COL1 = '" & m.COL1 & "' AND ID <= " & m.ID ) AS COL2a, ( SELECT Count(*) FROM MyTable AS m2 WHERE m2.COL1 = m.COL1 AND m2.ID <= m.ID ) AS COL2b FROM MyTable2 AS m ORDER BY m.ID;