ranking

[charlee63]

I am trying to Rank in Access.

Here is the Data.

[TABLE="width: 221"]
<tbody>[TR]
[TD]SNGL_SYS_ITM_ID[/TD]
[TD]END_EFCTV_DT[/TD]
[/TR]
[TR]
[TD]000000031[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000031[/TD]
[TD]12-Nov-18[/TD]
[/TR]
[TR]
[TD]000000031[/TD]
[TD]15-Nov-18[/TD]
[/TR]
[TR]
[TD]000000046[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000050[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000051[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000052[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000052[/TD]
[TD]16-Mar-17[/TD]
[/TR]
[TR]
[TD]000000055[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000000070[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

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

[TABLE="width: 285"]
<tbody>[TR]
[TD]SNGL_SYS_ITM_ID[/TD]
[TD]END_EFCTV_DT[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]26-Aug-16[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]14-Sep-17[/TD]
[TD]0[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Joe4]

What exactly are you trying to rank on?