Access 2010 Limit Query Results

Question

What's the syntax in MS Access 2010 for limiting the results of a query to the first 1,000?

I've tried this

SELECT tblGL.[Cost Centre Code]
FROM tblGL
LIMIT 1000;

but I get the error 'Syntax error in FROM clause'.

I've also tried setting the Max Records property but it doesn't seem to do anything - I still get 7,000+ results regardless of what value I enter into the Max Records field.

I also want to have a 2nd query which selects the next 25,000, starting from the 1,001st. Something like:

SELECT tblGL.[Cost Centre Code]
FROM tblGL
LIMIT 1001, 25000;

Answer 1

What then is the Access equivalent of MySQL: LIMIT 1001, 25000 (ie return 25,000 results starting from the 1,001st)?

Unfortunately, in MS Access this isn't as straightforward as in MySQL.
In Access, you need to work with nested subqueries.

Here' an answer of mine where I'm showing how to build the correct SQL string for paging in C#:
How to do MS Access database paging + search?

Taking the SQL string from that answer and inserting your table name and column names will result in this query:

select [Cost Centre Code] from tblGL
where [Cost Centre Code] in (
    select top 25000 sub.[Cost Centre Code]
    from (
        select top 26000 tab.[Cost Centre Code]
        from tblGL tab
        where 1=1
        order by tab.[Cost Centre Code]
    ) sub
    order by sub.[Cost Centre Code] desc
)
order by [Cost Centre Code]

This eliminates at least the need for basic C# knowledge, but I'm afraid you'll still be confused in case you don't know how subqueries work :-)

The problem is:
Access has no built-in way to directly get 25000 rows, but skip the first 1000.
The only thing that's possible is to get the first X rows.

So I'm doing this (from the inside to the outside):

1. Load the first 26000 rows
   (1000 + 25000, because we want to skip 1000 rows and then load 25000)

2. From this dataset, load the first 25000 rows, but order descending.
   This will effectively load row 26000 to 1001 (in that order, because we ordered the rows descending!)

3. To get the rows in ascending order, just load from the table again (and order ascending!), but only the rows with the Cost Centre Codes from step 2.

Got it?
Yes, it looks intimidating at first glance, but as soon as you "get" subqueries, it's actually not that difficult.

Answer 2

MySQL & SQLite use limit. For SQL Server & MS Access, you need to use top X in the select portion of your query:

select top 1000 tblGL.[Cost Centre Code] FROM tblGL

Answer 3

If you want to use the parameter

Select Top (@Rows) tblGL.[Cost Centre Code] FROM tblGL