I seek an MS Access Select Statement that will allow me to render
links for HTML display so that I can get records 1-10, 11-20, 21-30,
et al (via MS ASP, although the programming language is largely
irrelevant) to render search links such as is found at the bottom of
Google's Search Pages, vis-a-vis:

Result Page: Previous [1] [2] [3] [4] [5] [6] Next 

Essentially, I have 4 fields in my table (table name: [Customers]),
which are as follows:

[RecordID] - (AutoNumber ReplicationID) a ReplicationID that is both
unique (non-sequential), auto generated by MS Access, and is the
Primary Key
[CustomerID] - (Text) the Customer's unique ID Username
[Details] - (Text) Customer comments
[Timestamp] - (Date/Time) a timestamp such as "25/01/2006 11:27:43"

To get all of the records with the latest Timestamp at the top I could
use the following SQL statement:

SELECT *
FROM [Customers]
WHERE [CustomerID] = 'HarryHotdog'
ORDER BY Timestamp DESC;

and if I wanted to return the top 10 I could use

SELECT TOP 10 *
FROM [Customers]
WHERE [CustomerID] = 'HarryHotdog'
ORDER BY Timestamp DESC;

however how do I get records 11 - 20, 21 - 30, etc up to the total
number of records that could be returned by the first Select (above)?

Ideally, I don't want to have to add an additional table field called
[CustomerSequentialID] (number) as when I insert a new Customer Record
I would first have to count the existing number of rows that related
to the unique customer, then add 1 to that number and then include it
in [CustomerSequentialID] when I inserted the new record.

Hence the answer that I am seeking shall use only the above four
fields and shall cater for N1 and N2 variables where N1 and N2
represent 1 and 10, 11 and 20, 21 and 30, et al, hence returning
records 1 to 10, 11 to 20, 21 - 30, et al.

Kind regards,

Harry Hotdog

Hi,

While there doesn't seem to be a simple way to do this directly in
access SQL, if you have control over the SQL you're executing, and no
two rows have the same timestamp, you can do it as follows:

I'll use your N1 and N2 inclusive range as suggested, so the first
page is N1=1, N2=10, second is N1=11, N2=20 etc.

First page, as you rightly suggested:
SELECT TOP N2 *
FROM [Customers]
WHERE [CustomerID] = 'HarryHotdog'
ORDER BY Timestamp DESC;

Subsequent pages
SELECT TOP (N2-N1+1) *
FROM [Customers]
WHERE [CustomerID] = 'HarryHotdog'
AND Timestamp >
  (
    SELECT MAX(Timestamp)
    FROM
      (
        SELECT TOP N1-1 Timestamp
        FROM [Customers]
        ORDER BY Timestamp DESC
      )
  )
ORDER BY Timestamp DESC;

The *big* problem is that you stupidly can't parameterise the TOP
value in Access. So you have to change the SQL you're passing to
Access, rather than pass a parameter. This isn't always a bad idea,
and can often be simpler and more efficient than true parameters.

So, if you're using a basic like script (e.g. ASP's VBScript), you
could do something like that below, and it should be easy enough to do
in other languages.

if n1=1 then
  sSQL="SELECT TOP " & n2 & " *"
  sSQL=sSQL & " FROM [Customers]"
  sSQL=sSQL & " WHERE [CustomerID] = 'HarryHotdog'"
  sSQL=sSQL & " ORDER BY Timestamp DESC;"
else
  sSQL="SELECT TOP (" & n2-n1+1 & " *"
  sSQL=sSQL & " FROM [Customers]"
  sSQL=sSQL & " WHERE [CustomerID] = 'HarryHotdog'"
  sSQL=sSQL & " AND Timestamp >"
  sSQL=sSQL & "   ("
  sSQL=sSQL & "     SELECT MAX(Timestamp)"
  sSQL=sSQL & "     FROM"
  sSQL=sSQL & "       ("
  sSQL=sSQL & "         SELECT TOP " & n1-1 & " Timestamp"
  sSQL=sSQL & "         FROM [Customers]"
  sSQL=sSQL & "        )"
  sSQL=sSQL & "  )" 
  sSQL=sSQL & " ORDER BY Timestamp DESC;"
end if

myRecordset=myConnection.execute(sSQL)

Andy,

May I thank-you for your astonishing result, I hadn't imagined that
I'd have to go to the complexity of nesting Select statements two
deep.

Thank-you also for the amount of time that you put into the solution,
you have my recommendation to join the Google Answers Team so that you
get paid for answering my next query!

Kind regards,

Harry

Actually ADO Recordset has something built-in to handle this. The
Recordset property called PageSize, which lets you specify how may
rows you want on a page, and the AbsolutePage property lets navigate
to the various pages, There is a VB example here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdproabpagex.asp

With this technique your select statement is simply select * from...
The Recordset object handles the rest.