asked on

SQL Statement to work on a Micrsoft Access Database which will get Top 5 Records but Skip the first 5

I need help with an SQL Statement to work on a Micrsoft Access Database which has a table named Books. I would like to get Top 5 Records but skiping the first 5 records.
I am therefore looking for an SQL equivalent of the .Skip() method in LINQ which will work on an Micrsoft Access Database.

Thank you for your help.

Gustav Brock

Try this:

Select Top 5 * 
From Books
Where ID Not In (Select Top 5 * From Books Order By ID Desc)

Open in new window

FaheemAhmadGul

ASKER

Thank you for your comment.
I need to put this query in a string Variable and I am doing it like this:

strSelectBooks = "Select Top * 5  From Books Where ID Not In (Select Top 5  *  from Books Where Person='Manager')"

Open in new window

However, the above query string gives an error. Please let me know if I am doing it correctly.

Gustav Brock

Hard to tell with neither the table nor the error. But I had left out the last order by so try:

strSelectBooks = "Select Top * 5  From Books Where ID Not In (Select Top 5  *  from Books As T Where T.Person='Manager' Order By T.ID Desc) Order By ID Desc"

Open in new window

FaheemAhmadGul

ASKER

This also give an error. The error I get is
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Error Number: -2147217900

Gustav Brock

That typically indicates a missing or misspelled field or table name.

With no further info, I can't tell more. Double-check.

FaheemAhmadGul

ASKER

I have double checked. I am sure the Table and Field names are correct. Could it be that this works with SQL Server but not with Access?

FaheemAhmadGul

ASKER

I have tried this query in an SQL Server and in that also it shows an error. Please note that in the SQL Server the table is Tasks instead of Books.
I include a screen shot of where SQL points the error is.

Gustav Brock

Oh, sorry, it should read:

strSelectBooks = "Select Top 5 * From Tasks Where ID Not In (Select Top 5  *  from Tasks As T Where T.Person='Manager' Order By T.ID Desc) Order By ID Desc"

Open in new window

However, as you have seen on SO, a faster method could be to pick first Top 10, then Top 5 of these:

strSelectBooks = "Select Top 5 * From (Select Top 10  *  from Tasks Where Person='Manager' Order By ID Desc) As Tasks10 Order By ID Asc"

Open in new window

Hamed Nasr

Try this modification to Gustav's code

strSelectBooks = " Select * From (Select Top 5 * From (Select Top 10 * from Tasks Where Person='Manager' Order By ID ) As Tasks10 Order By ID Desc) Order By ID"

John Tsioumpris

You could also fetch top 10 records and skip while processing the first 5

FaheemAhmadGul

ASKER

Thank you all experts for your comments. I have tried all the suggestions but none is giving the results - some give erros and the one that does get some results does not get the records I would like to fetch.

A screen shot of trial all three queries will explain what I mean.

Hamed Nasr

The example presented in the screenshot is from SQL server.
Check this Statement, that works for table A and field a.

SELECT *
FROM (SELECT TOP 3 * 
FROM (SELECT TOP 6 * FROM A ORDER BY a DESC) as top10 )AS tbl ORDER BY a

Open in new window

Try this modification of the statement in the screenshot.

SELECT *
FROM (SELECT TOP 5 * 
FROM (SELECT TOP 10 * FROM Tasks WHERE Person='Manager' ORDER BY ID DESC) as top10 )AS tbl ORDER BY ID

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FaheemAhmadGul

ASKER

This works perfectly. Many thanks. I am very grateful.
My apologies for the delay in accepting the solution - I was out of the town.

Gustav Brock

No problem. Have a nice weekend!