How to show row number in Access query like ROW_NUMBER in SQL

Question

I have a table in Microsoft Access, and I want to show row number in a column using a select query in Access just like using ROW_NUMBER() function in SQL Server.

In SQL Server, I can using this query:

SELECT ROW_NUMBER() OVER (ORDER BY tblUser.UserID) AS NoRow, * 
FROM tblUser

I use same query in access, but I get error.

Can you help me?

Answer 1

You can try this query:

Select A.*, (select count(*) from Table1 where A.ID>=ID) as RowNo
from Table1 as A
order by A.ID