Paged query

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello, is it possible using SQL to return results in
sections or pages? For example here is my query

SELECT TOP 20 accounts.Username as Username,
eb.ScoreTotal as ScoreTotal, eb.SecPlayed as SecPlayed,
eb.LevelNum as LevelNum FROM accounts,eb WHERE
accounts.Id = eb.GameId ORDER BY eb.ScoreTotal DESC,
eb.Id;

This gets the top 20 scores, how can I display scores 21-
40, and so on?

(I'm using ASP and an Access 2000 mdb)
 
You'll most likely be using ADO in an Active Server Page, so you can set the
PageSize of the recordset and navigate by page. I found this example in
Help:

Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable

' Display names and hire dates, five records
' at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top