Re: TOP in query user selects amount to return

Tech-Archive recommends: Fix windows errors by optimizing your registry

Select Top N records where N is variable ( a parameter)

One method that can be used is to use a ranking query to assign a number to the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Kevin wrote:
Thanks Dennis,

I tried that also but, like you, just get errors!

"Dennis" wrote:

I don't think so. I tried putting this into the SQL view of a query but it errors out
SELECT TOP [Enter How Many] etc. etc.

"Kevin" wrote:

Thanks Dennis,

I am sure this will work, and can use it if I need to but, do you know if there is a way to do this from within the query rather than through a form?

"Dennis" wrote:

Forget my last post, I have just realised you want the user to supply how many records to return. You could try building the SQL string and incorporating a textbox from a form as the amount to return
e.g.
strSQL = "SELECT TOP " & Forms![FormName]![TextBoxName] & " etc etc

"Kevin" wrote:

Using Top, is there a way to ask the user how many records to return? if not is there another simple way of acheiving this?
.

Relevant Pages

  • Re: "Pro" Fast LarryGunninger
    ... UPA 2006 Calendar Ranking ... Johnny Archer 280 ... 16 Tony Crosby 95 ... 21 Dennis Orcollo 85 ...
    (rec.sport.billiard)
  • Re: Ranking in Queries
    ... >I am trying to add a ranking expression to my via via subquery. ... >The rank should be descending. ... >SELECT ASXCode, Traded_12mth, ... A ranking query will rank those as 1,2,2,4, not 1,2,2,3. ...
    (microsoft.public.access.queries)
  • Re: Ranking Report
    ... In the column under ranking it prints the word "error" for each of the ranking placements instead of the number ... "fredg" wrote: ... >> I have created a ranking query that works just fine. ...
    (microsoft.public.access.reports)