-------------------------------------------------------------------------
--how to create a dense_rank of data in a table compatible with MSAccess
-------------------------------------------------------------------------
1. Get the distinct rows into a table (or query in Access)
SELECT EntryDate INTO #query1 FROM tblInvMovement
GROUP BY EntryDate;
--2. Number the rows by using a self join, looking back at previous, or inner rows
SELECT A.EntryDate, Count(*) as dense_rank
INTO #query2
FROM #query1 A JOIN #query1 B
ON B.EntryDate <= A.EntryDate
GROUP BY A.EntryDate;
--3. Join the dense_rank to the rest of the data
SELECT A.*, B.dense_rank
FROM tblInvMovement A JOIN #query2 B
ON A.EntryDate = B.EntryDate
ORDER BY A.EntryDate;
-- To do this in one step using MSSQL:
SELECT *, DENSE_RANK() OVER(ORDER BY EntryDate ASC) FROM tblInvMovement;