How to use the same function like Oracle Rownum in MS ACCESS

sql ms-access rownum
Kun-Yao Wang picture Kun-Yao Wang · Dec 18, 2013 · Viewed 14.6k times · Source

I am encountering a problem, I had done a function that the data can be loaded by detecting scrolling position, the function was made with a SQL statement "Rownum", it only works in Oracle, but not in ACCESS.

I would like to query the data and resort it

ID  value
1   aa
3   bb

with Rownum we can do like this

NID ID value
1   1  aa
2   3  bb

how can I write a SQL statement with Microsoft ACCESS

Answer

HansUp picture HansUp · Dec 18, 2013

Access does not support that function. If your ID field is a numeric primary key, you can include a field expression which is the count of the number of rows with ID <= to the current ID value.

SELECT
    DCount('*', 'YourTable', 'ID <= ' & y.ID) AS NID,
    y.ID,
    y.value
FROM YourTable AS y;

You could use a correlated subquery instead of DCount if you prefer.

And ID does not actually have to be a primary key. If it has a unique constraint it is still suitable for this purpose.

And the targeted field does not absolutely have to be a number, but text data type can be more challenging.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More