TOP in query user selects amount to return

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

 

[Dennis]

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

 

[Dennis]

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]

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?

 

[Dennis]

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?

 

[Kevin]

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?

 

[John Spencer]

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?

 

[Michel Walsh]

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.

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?

 

[John Spencer]

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

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.

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?

 

[Michel Walsh]

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

 

[cpsaltis]

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