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.
LVL 1
FaheemAhmadGulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try this:

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

Open in new window

0
FaheemAhmadGulAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

FaheemAhmadGulAuthor Commented:
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
0
Gustav BrockCIOCommented:
That typically indicates a missing or misspelled field or table name.

With no further info, I can't tell more. Double-check.
0
FaheemAhmadGulAuthor Commented:
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?
0
FaheemAhmadGulAuthor Commented:
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.Error Message Screen shot in SQL ServerError Message Screen shot in SQL Server
0
Gustav BrockCIOCommented:
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

0
Hamed NasrRetired IT ProfessionalCommented:
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"
0
John TsioumprisSoftware & Systems EngineerCommented:
You could also fetch top 10 records and skip while processing the first 5
0
FaheemAhmadGulAuthor Commented:
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. Three proposed queries and results
0
Hamed NasrRetired IT ProfessionalCommented:
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

0
Gustav BrockCIOCommented:
I need help with an SQL Statement to work on a Micrsoft Access Database

Then it doesn't make much sense to check our code in SSMS.
So, back in Access, this does provide the result requested:

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

EE-all.pngEE-Top-10-5.png
For the reverse order:

SELECT *
FROM
    (SELECT TOP 5 *
    FROM 
        (SELECT Top 10 *
        FROM Tasks
        WHERE Person="Manager"
        ORDER BY ID ASC)  AS Tasks10
    ORDER BY ID DESC) AS Tasks5
ORDER BY ID ASC

Open in new window

EE-10-5.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaheemAhmadGulAuthor Commented:
This works perfectly. Many thanks. I am very grateful.
My apologies for the delay in accepting the solution - I was out of the town.
0
Gustav BrockCIOCommented:
No problem. Have a nice weekend!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.