TOP in query user selects amount to return

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

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?
 
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
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dennis,

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

Dennis said:
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 said:
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 said:
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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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
Thanks Dennis,

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

Dennis said:
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 said:
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?

:

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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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
Thanks Dennis, I tried that also but, like you, just get errors!

Dennis said:
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.

:

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?

:

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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Thanks Dennis, I tried that also but, like you, just get errors!

:

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.

:

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?

:

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

:

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?
Click to expand...
Click to expand...
Click to expand...
 
Just as Ken Getz once said: "If I have thought about it, all by
myself, it is quite possible that someone ELSE have thought about it too,
all by himself..."

It is not as much as the idea than the way you expressed it that
'matters', in the end. And definitively, *you* typed your post! :-)


Vanderghast, Access MVP
 
Michel & John,

I wanted to thank you for your help getting a variable TOP form working
(includes values and percentages) as well as Michel's help with complex
query. Your alternative to null param form fields simplified everthing.

Costas
 
Back
Top