Query to select recordset

J

jkmccnh

Using Access 2000 I need a query which will allow the user to select the top
50 records based on sales (this part is easy using the TOP predicate). The
problem is the user also needs to be able to select the NEXT set of 50 (ie
51-100 in order of sales rank) etc in groups of 50. The table ("Sales") has
an autonumber field ("SalesID") as a primary key as well as a field
("SalesAmt"). I can't figure out how to write this query.
 
N

Nikos Yannacopoulos

You could do it with a sub-query like:

SELECT TOP 50 SalesID, SalesAmt FROM Sales
WHERE SalesID Not In (SELECT TOP 50 SalesID FROM Sales ORDER BY SalesAmt
DESC)
ORDER BY SalesAmt DESC

Likewise, you change 50 to 100 in the subquery for the next 50, etc. Be
warned, though, it will be slow.

HTH,
Nikos
 
N

Nikos Yannacopoulos

Jamie,

Changing the number of records in the SQL statement is no problem,
actually it's a piece of cake with some VBA code. The real problem is
indeed performance. Come to think of it, it might be worth using a temp
table instead, as far as this approach goes.
Your approach sounds very interesting; regrettably (for me, being a DAO
guy) PageSize is only ADO, I'm afraid... looks like if I ever need this
I'll have to get into ADO!

Regards,
Nikos
 
Top