Re: TOP in query user selects amount to return

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

Whoops, I just noted that I forgot to credit the original author of that solution. My apologies to Michel Walsh from whom I copied this solution.

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

Michel Walsh wrote:
Indeed. In addition, if someone is using MS SQL Server 2005 instead of Jet 4.0, you can use a parameter but take note that ( ) are mandatory in that case:


SELECT TOP (@N) ...



There is no equivalent 'immediate' solution with Jet.




"John Spencer" <spencer@xxxxxxxxx> wrote in message news:%234NhZDH0IHA.5520@xxxxxxxxxxxxxxxxxxxxxxx
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: what does this mean?
    ... I sometimes have a tendency to see things backwards. ... "Michel Walsh" wrote: ... Vanderghast, Access MVP ... "John Spencer" wrote: ...
    (microsoft.public.access.queries)
  • Re: Ranking difficulty
    ... John Spencer ... Access MVP 2002-2005, 2007 ... I created a ranking by ranking the scores and named it RANKID. ...
    (microsoft.public.access.queries)