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

Question

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

Solution

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.

Comments

[Kun-Yao Wang]

Thanks for your answer!It looks almost done, but I would like to plus "where" like this"SELECT DCount('*', 'Article', 'ID <= ' & y.ID) AS NID, y.ID, y.Topic FROM Article AS y where NID between 1 and 2" because I would like to specify load range of items~~ I add like that but it does not work..

[HansUp]

Consider TOP 2 ... SELECT TOP 2 DCount('*', 'Article', 'ID <= ' & y.ID) AS NID, y.ID, y.Topic FROM Article AS y ORDER BY y.ID;

[Kun-Yao Wang]

And also thanks for revising my format, it looks better indeed!

[Kun-Yao Wang]

is it possible being a range? between 1 and 2 is just a example~~ once if I want to select between 15 and 30, how can I make it? Thanks!

[HansUp]

Save the first query (the one without TOP) as qryStep1. Then create a second query which uses the first as its data source: SELECT * FROM qryStep1 WHERE NID BETWEEN 15 AND 30;