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

  • Thread starter Thread starter Mic Diehl
  • Start date Start date
M

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!
Thanks
Mic
 
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
Me.Requery

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.
 
Select *
From
(
Select
Row_Number() Over (Order By <some field> ) As RowNum
,*
From table
) t
Where RowNum between @n1 and @n2
 
Back
Top