Running count over a group in Query (1 Viewer)

shacdactvm

New member
Local time
Today, 12:36
Joined
Feb 2, 2015
Messages
8
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

Super Moderator
Staff member
Local time
Today, 07:06
Joined
Feb 19, 2013
Messages
16,553
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

Code:
 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

New member
Local time
Today, 12:36
Joined
Feb 2, 2015
Messages
8
Million Thanks [FONT=&quot]CJ_London. Its awesome.. [/FONT]
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:06
Joined
Feb 19, 2013
Messages
16,553
no problem - it is a common question:)
 

Users who are viewing this thread

Top Bottom