I am trying to Rank in Access.
Here is the Data.
<tbody>
</tbody><colgroup><col><col></colgroup>
Here is the SQL
SELECT T1.[SNGL_SYS_ITM_ID], T1.[END_EFCTV_DT],
(SELECT COUNT(T1.[SNGL_SYS_ITM_ID])
FROM
[Rank Query] T2
WHERE
T2.[SNGL_SYS_ITM_ID] = T1.[SNGL_SYS_ITM_ID] ) AS Rank
FROM
[Rank Query] T1
ORDER BY [SNGL_SYS_ITM_ID], [END_EFCTV_DT]
Result
<tbody>
</tbody><colgroup><col><col><col></colgroup>
Here is the Data.
SNGL_SYS_ITM_ID | END_EFCTV_DT |
000000031 | |
000000031 | 12-Nov-18 |
000000031 | 15-Nov-18 |
000000046 | |
000000050 | |
000000051 | |
000000052 | |
000000052 | 16-Mar-17 |
000000055 | |
000000070 |
<tbody>
</tbody><colgroup><col><col></colgroup>
Here is the SQL
SELECT T1.[SNGL_SYS_ITM_ID], T1.[END_EFCTV_DT],
(SELECT COUNT(T1.[SNGL_SYS_ITM_ID])
FROM
[Rank Query] T2
WHERE
T2.[SNGL_SYS_ITM_ID] = T1.[SNGL_SYS_ITM_ID] ) AS Rank
FROM
[Rank Query] T1
ORDER BY [SNGL_SYS_ITM_ID], [END_EFCTV_DT]
Result
SNGL_SYS_ITM_ID | END_EFCTV_DT | Rank |
26-Aug-16 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 | |
14-Sep-17 | 0 |
<tbody>
</tbody><colgroup><col><col><col></colgroup>