Thread: Running Count Query

I need help with a Running Count Query.
Lets say i have one column,

X
X
X
T
T
E
E
E
E
E

i want to make a query that adds a running count column, like the one below

X ,1
X ,2
X ,3
T ,1
T ,2
E ,1
E ,2
E ,3
E ,4
E ,5

Does anyone know how to do this? any help is GREATLY appreciated.

Originally Posted by Shijesh Kumar

Suppose ur data is in Column A.

In B1 you type 1 ( which is next to first X )

in B2 type

Code:

 =IF(A2=A1,B1+1, 1)

and drag it down

Im sorry.. i'm using Microsoft Access.. so i can't do what you mentioned

You can do it using a subquery, but you'll need an ID field (autonumber) to pull it off. Here is the syntax:

Code:

SELECT a.myField, (Select Count(b.myField) FROM myTable AS b WHERE b.myField=a.myField and b.ID<=a.ID) AS Ctr
FROM myTable AS a;

myField is your field name
mytable is your table name
ID is the autonumber field

Brent