MS Access Rank – Using RANK() Function in MS Access

ms access

I have a table in Access like this: 

ID |  Op_ID  
1  |  1  
1  |  2  
2  |  3  
3  |  4  
3  |  5  
3  |  6

Is there a SELECT query that I can run in Access that would give me something like this?: 

ID |  Count(ID)  
1  |  1  
1  |  2  
2  |  1  
3  |  1  
3  |  2  
3  |  3

So basically I want a Count of ID that loops over ID every time ID changes.

Best Answer

This query simulates a RANK() function.

SELECT T1.ID, (SELECT COUNT(*) 
               FROM YOUR_TABLE T2 
               WHERE T1.ID = T2.ID AND T1.OP_ID >= T2.OP_ID) AS Rank 
FROM YOUR_TABLE T1
ORDER BY ID, OP_ID

It works only if there is an unique ID field that allow to count records where:

T1.OP_ID >= T2.OP_ID

enter image description here

Related Question