How does Access handle LIMIT statement? 1

[BlindPete]

I can use TOP, but that only works from the top. I need to paginate the data, say 25 records at a time. How do I do that in Access?

SELECT TOP 25 usc_Detail.* FROM usc_Detail,

but how would I fetch just records 26-50?

In SQL Server I can use LIMIT 26, 25


-Pete
Games the old fashion way with Dice, Paper and Pencils!

 

[AlexCuse]

Ok, so you have this:

SELECT TOP 25 usc_Detail.* FROM usc_Detail

Now, if you want the next 25, you can do this (assumes some kind of PK value)

select top 25 usc_Detail.* from usc_Detail
where usc_Detail.PK not in (select top 25 usc_Detail.PK from usc_Detail)

One note - whenever you use TOP, specify an ORDER BY as well. Without an order by there is no guarantee you will get the same top 25 each time.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[BlindPete]

Ahh ok so and pardon my ignorance on this,

Get the first 10

select top 10 usc_Detail.* from usc_Detail ORDER BY usc_Detail.somefield
where usc_Detail.PK not in (select top 25 usc_Detail.PK from usc_Detail)

Get the 2nd 10

select top 20 usc_Detail.* from usc_Detail ORDER BY usc_Detail.somefield
where usc_Detail.ID not in (select top 10 usc_Detail.ID from usc_Detail ORDER BY usc_Detail.somefield)

And so on?


-Pete
Games the old fashion way with Dice, Paper and Pencils!

 

[BlindPete]

Sorry the 1st query would not have the where clause.

-Pete
Games the old fashion way with Dice, Paper and Pencils!

 

[AlexCuse]

one change to the second (you want next 10 not next 20):

select top 10 usc_Detail.* from usc_Detail ORDER BY usc_Detail.somefield
where usc_Detail.ID not in (select top 10 usc_Detail.ID from usc_Detail ORDER BY usc_Detail.somefield)

so 21-30 would be:

select top 10 usc_Detail.* from usc_Detail ORDER BY usc_Detail.somefield
where usc_Detail.ID not in (select top [b]20[/b] usc_Detail.ID from usc_Detail ORDER BY usc_Detail.somefield)

You only want to change the number in the subquery.

Make sense?

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[BlindPete]

Oh duh, of coarse you are correct.

Thanks for all the help.

-Pete
Games the old fashion way with Dice, Paper and Pencils!