Add a row number to a microsoft access query

I have a SELECT DISTINCT query result to which I would like to add a row number to each row. I have tried ROW_NUMBER OVER… syntax, but after a lot of research I think that it turns out this is not supported in ACCESS.

I have a table of information called – SYNC002_ACCESS and the query below:

SELECT DISTINCT pluquo
FROM SYNC002_ACCESS;

Which gives me a list of distinct values, 336 rows. What I need is to add a second column to that query which will have numbers for each row : 1/2/3/4/5……335/336.

I will really appreciate if someone is able to help me as I have been struggling with this quite a while now. Thank you

Answer

One way to do it is with a correlated subquery:

SELECT s.pluquo,
              (SELECT COUNT(*) 
               FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS t
               WHERE t.pluquo <= s.pluquo
              ) AS row_number
FROM (
  SELECT DISTINCT pluquo
  FROM SYNC002_ACCESS
) AS s

Or with a self join:

SELECT s1.pluquo, COUNT(*) AS row_number
FROM (SELECT DISTINCT pluquo  FROM SYNC002_ACCESS) AS s1
INNER JOIN (SELECT DISTINCT pluquo  FROM SYNC002_ACCESS) AS s2
ON s2.pluquo  <= s1.pluquo 
GROUP BY s1.pluquo