Access request yielding results like ROW_NUMBER () in T-SQL

Question

Do we have a ROW_NUMBER function in MS Access? If so, please let me know any syntax for it, as I am stuck here. I tried forums, but I get SQL server syntax. Below is my request:

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 a syntax error.

Answer 1

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

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

request

 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 this as a subquery in our JOIN for another 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