Running count over a group in Query

[shacdactvm]

Dear All,

I have an access query named "leaveapp" and I want a running count as below:

EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1

and i need a column in the right with running count like below

EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1

Any help in this regard is greatly appreciated

Regards
Sha

 

[CJ_London]

to do what you want you need at least one more field (does not need to be displayed) and typically will be the primary key - for the purposes of this example I'll call it repPK

 SELECT EmpID, TypeID, (SELECT count(*) FROM myTable as T where EmpID=myTable.EmpID and TypeID=myTable.TypeID and repPK<=myTable.repPK) as runningcount
 FROM myTable
 ORDER BY EmpID, TypeID, RepPK

 

[shacdactvm]

Million Thanks [FONT=&quot]CJ_London. Its awesome.. [/FONT]

 

[CJ_London]

no problem - it is a common question