TOP in query user selects amount to return

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?

Just put the number you want after Top
SELECT TOP 10 .....

"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?

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?

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?

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?

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?

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?

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" wrote in message
...
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?

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" wrote in message
...
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?

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