Access query producing results like ROW_NUMBER() i

Question

Do we have ROW_NUMBER function in MS Access? If it has then please let me know any syntax for it as I am stuck here. I have tried forums but I get sql server syntax. Following is my query:

select 
    ROW_NUMBER() OVER (ORDER BY t.TID) AS uni , 
    t.TSource as [Source],
    t.TText as [Text],
    u.Name as [UserId],
    u.Image_Url as [ImageFilePath], 
from table1 t inner join table2 u on t.UserId = u.UIds

but it gives syntax error.

Answer 1

In Access SQL we can sometimes use a self-join to produce a rank order. For example, for [table1]

TID  UserId  TSource  TText
---  ------  -------  -----
412  homer   foo      bar  
503  marge   baz      thing
777  lisa    more     stuff

the query

SELECT 
    t1a.TID, 
    t1a.UserId, 
    t1a.TSource, 
    t1a.TText,
    COUNT(*) AS TRank
FROM
    table1 AS t1a
    INNER JOIN
    table1 AS t1b
        ON t1a.TID >= t1b.TID
GROUP BY
    t1a.TID, 
    t1a.UserId, 
    t1a.TSource, 
    t1a.TText

produces

TID  UserId  TSource  TText  TRank
---  ------  -------  -----  -----
412  homer   foo      bar        1
503  marge   baz      thing      2
777  lisa    more     stuff      3

and we can use that as a subquery in our JOIN to the other table

select 
    t.TRank as uni,
    t.TSource as [Source],
    t.TText as [Text],
    u.Name as [UserId],
    u.Image_Url as [ImageFilePath]
from 
    (
        SELECT 
            t1a.TID, 
            t1a.UserId, 
            t1a.TSource, 
            t1a.TText,
            COUNT(*) AS TRank
        FROM
            table1 AS t1a
            INNER JOIN
            table1 AS t1b
                ON t1a.TID >= t1b.TID
        GROUP BY
            t1a.TID, 
            t1a.UserId, 
            t1a.TSource, 
            t1a.TText
    ) AS t 
    INNER JOIN 
    table2 AS u 
        ON  t.UserId = u.UIds

producing something like

uni  Source  Text   UserId        ImageFilePath
---  ------  -----  ------------  -------------
  1  foo     bar    HomerSimpson  whatever1    
  2  baz     thing  MargeSimpson  whatever2    
  3  more    stuff  LisaSimpson   whatever3