ranking

[charlee63]

I am trying to Rank in Access.

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>

 

[Joe4]

What exactly are you trying to rank on?