How to select rows m through n in access?

Question

I am modifying a query for a sub-report in Access 2016 and need to select a set of rows, not all rows. By default the query generated looks like:

SELECT table_name.a, table_name.b, table_name.c
FROM table_name
WHERE (((table_name.dist_ft)<3001));

How can I select only rows m through n instead of all rows?

Thanks for your insights! ... [edit]

An additional clarification - when I run the query like

SELECT TOP 16 * FROM table_name WHERE (((table_name.dist_ft)<3001));

... or any other variation I've tried with TOP my sub-report does not get populated. It only contains data when all fields are selected and TOP is not used. I must be missing something.

Possible duplicate of [MS Access LIMIT X, Y](https://stackoverflow.com/questions/8627032/ms-access-limit-x-y) Simple two top nested queries (derived tables) with the needed order by. or get top n where ID not in top m — xQbert, Nov 03 '17 at 17:41

OwlsSleeping · Answer 1 · 2017-11-05T22:59:36.893

0

Is ID your "row number" here?

SELECT table_name.a, table_name.b, table_name.c 
FROM table_name 
WHERE table_name.dist_ft<3001
AND table_name.ID>=m
AND table_name.ID<=n
;

Updated with more general case based on comments-

Select table_name.a, table_name.b, table_name.c from tablename 
where tablename.id in 
(select top n tablename.id from tablename) 
and tablename.id not in 
(select top m tablename.id from tablenane)

edited Nov 05 '17 at 22:59

answered Nov 03 '17 at 17:29

OwlsSleeping

1,487
2
11
19

Thanks for your response. "ID" is a field in table_name i.e. it is not the row number. Is there a system field that may be the row number otherwise? I understood your suggestion at the latter half of your response; however, I was not able to get SELECT TOP 16 table_name.a, table_name.b, table_name.c ... to work! It seemed like having a, b, c, etc. was throwing off TOP. The TOP examples I kept seeing on other forums just had one e.g. table_name.a. Thanks again. – Kevin R. M. Nov 03 '17 at 20:45
Sorry was rushed at the end earlier. Select a,b,c from tablename where id in (select top n id from tablename) and id not in (select top m from tablenane) – OwlsSleeping Nov 05 '17 at 16:58

Thorsten Kettner · Answer 2 · 2017-11-05T23:30:58.400

Records m to n are the records 1 to n minus the records 1 to m-1. Be aware though that you need an ORDER BY clause for a TOP clause to make sense.

Here is an example with m = 31 to n = 40 and an order by all three selected columns. MS Access does not support EXCEPT so we cannot subtract the two data sets, which would be the straight-forward way to go. We could also express the desired result as the top n where (a,b,c) not in top m-1, but MS Access does not support an IN clause on multiple columns either. So I am using an anti join here (for which I select dist_ft, but it can be any non-nullable column of the table).

In case your table has a unique ID column, you can use a more readable where (id) not in (select top 30 id ...) instead of an anti join. In any way make sure to apply the same WHERE clause (dist_ft < 3001 in your case) and ORDER BY clause (e.g. ORDER BY a, b, c) to the main query and subquery.

SELECT TOP 40 a, b, c
FROM table_name t
LEFT JOIN
(
  SELECT TOP 30 a, b, c, dist_ft
  FROM table_name
  WHERE dist_ft < 3001
  ORDER BY a, b, c
) no ON no.a = t.a AND no.b = t.b AND no.c = t.c
WHERE t.dist_ft < 3001
AND no.dist_ft is null
ORDER BY t.a, t.b, t.c;

MS Access is known for requiring additional parentheses on multiple joins. I cannot say whether above query works straight away or if parantheses must be added somewhere.

score 0 · Answer 3 · answered Nov 05 '17 at 23:38

You sort thrice to get the result you are after. Let's say we want rows 31 to 40:

Sort and get top 40
sort in reverse order and get top 10
sort again to get the order you actually want

The query:

SELECT a, b, c
FROM
  SELECT TOP 10 a, b, c
  FROM
  (
    SELECT TOP 40 a, b, c
    FROM table_name
    WHERE t.dist_ft < 3001
    ORDER BY a, b, c
  ) top_n
  ORDER BY a desc, b desc, c desc
) top_m_to_n
ORDER BY a, b, c;

How to select rows m through n in access?

3 Answers3