Re: TOP in query user selects amount to return
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 17 Jun 2008 09:50:27 -0400
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?
.
- Follow-Ups:
- Re: TOP in query user selects amount to return
- From: John Spencer
- Re: TOP in query user selects amount to return
- References:
- Re: TOP in query user selects amount to return
- From: John Spencer
- Re: TOP in query user selects amount to return
- Prev by Date: EXTRACT DATA FROM FIELD
- Next by Date: Re: Top N Records on multiple fields
- Previous by thread: Re: TOP in query user selects amount to return
- Next by thread: Re: TOP in query user selects amount to return
- Index(es):
Relevant Pages
|
