In Access97 SQL, how do I return a row of rows?

Question

I have an Access97 database (unfortunately) and I query it through ODBC.

I want to build a query that returns me J ordered rows starting at offset K. Right now for the related operation, I use the subquery and the TOP keyword, this works fine, but I don't think I can use it for this new operation, which I trying to execute. I want to do this because I want to add some form of swap to some software.

Ideally, the user interface should be able to say give me 50 records, starting with 150 records, sorted by date column.

Any help would be appreciated.

Answer 1

ROW_NUMBER() Access. , Access . .

, :

SELECT t1.ID, t1.SomeText, t1.SomeDate,
(
  SELECT COUNT(*) + 1
  FROM Table1 AS t2
  WHERE t1.SomeText > t2.SomeText
) as RowNumber
FROM Table1 t1
ORDER BY t1.SomeText

( ):

ID | SomeText             | SomeDate  | RowNumber
10 | 4NGJN                | 1/20/2010 | 1
11 | ABH                  | 1/20/2010 | 2
2  | asldkfj              | 1/20/2010 | 3
1  | asldkfjaslgh         | 1/20/2010 | 4
7  | ewoiuhdkjnlbkjbn     | 1/20/2010 | 5
4  | oihkjldhnlkmdfn,mn   | 1/20/2010 | 6
3  | oihoih               | 1/20/2010 | 7
5  | qwwern               | 1/20/2010 | 8
8  | SKN                  | 1/20/2010 | 9
9  | WEOIN                | 1/20/2010 | 10
6  | wetrhn               | 1/20/2010 | 11
12 | XDNKJ                | 1/20/2010 | 12

Access ( SQL Server), :

SELECT ID, SomeText, SomeDate, RowNumber
FROM myView
WHERE RowNumber > 5
  AND RowNumber < 11

, "5" "11" , . (, 50 100 - ).

Answer 2

TOP Not In TOP:

 SELECT TOP n ... ID Not In (SELECT TOP k ...) 

, , Access