3

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

Kun-Yao Wang
  • 192
  • 1
  • 3
  • 16

1 Answers1

3

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 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.. – Kun-Yao Wang Dec 18 '13 at 08:18
  • 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;` – HansUp Dec 18 '13 at 08:21
  • And also thanks for revising my format, it looks better indeed! – Kun-Yao Wang Dec 18 '13 at 08:22
  • 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! – Kun-Yao Wang Dec 18 '13 at 08:24
  • 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;` – HansUp Dec 18 '13 at 08:27