Query to select recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Nikos said:
You could do it with a sub-query like:

SELECT TOP 50 SalesID, SalesAmt FROM <<snipped>>
Click to expand...

As you imply, this approach will result in multiple database round
trips. Also the 50 would have to be hard coded (although you could use
a different SQL construct if the 50 was required to be a parameter
value).

Perhaps a better approach would be to create a ranking column in the
query then use this value with the ADO Recordset's paging functionality
to get 50 records at a time:
e.g.

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

"Use the PageSize property to determine how many records make up a
logical page of data. Establishing a page size allows you to use the
AbsolutePage property to move to the first record of a particular page.
This is useful in Web-server scenarios when you want to allow the user
to page through data, viewing a certain number of records at a time."

I'm not sure whether the DAO Recordset has the same functionality.

Jamie.

--
 
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
 
Back
Top