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

ms-access-2007 row-number
Fauzi88 picture Fauzi88 · Feb 4, 2013 · Viewed 100.7k times · Source

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

JokoSumanto picture JokoSumanto · Jul 25, 2013

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

Related questions

Now() function with time trim

So the function =Now()....is there a way I can use this and only get the date, not the time? or is there just a function for this idea?

Read More
A select query selecting a select statement

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread). So I made a query that combines all of the …

Read More
Determine whether a Access checkbox is checked or not

Such a simple question, but I cannot find the answer (Google, MS help, SO): How can I check by VBA whether an unbound checkbox on an Access form is checked by the user or not? Can't find the right property. …

Read More