Display 1st 10 records, next 10 records using SQL?


Mic Diehl

Is it possible to display a group of records in 10 record increments using
an SQL statement, if not then How? I want to show 10 records at a time then
if that isn't what they want, show 10 more.
Please help!

PC Datasheet

Add a field to your table and call it Selected. Create a query that includes the
fields you want to display from your table and include Selected. Set the sort
order to what you want. Set the criteria for the Selected field to False. Right
click in the query window (where the table is at). A dialog appears; set the Top
property to 10.

Create another query based on the first query. Just include the Selected field.
Change the query to an Update query. Update the Selected field to True.

Base your form on the first query.

Place a command button on your form and put code in the Click event:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfUpdateQuery"
DoCmd.SetWarnings True

The first query will display the first 10 records in the sort order where
Selected is False. The first time you click the command button, Selected in the
first 10 records gets set to True so when you requery, you then display records
11 to 20. The same thing happens each time you click the button, you display the
next 10 records.

Left for you to do ---
Create a third Update query that resets Selected in all the records to False.
Run this query in the Close event of the form.
Aug 1, 2015
Reaction score
Select *
Row_Number() Over (Order By <some field> ) As RowNum
From table
) t
Where RowNum between @n1 and @n2

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question