Retrieving variable number of rows

[Amy Blankenship]

Hi, all;

I need to be able to use one query to retrieve either the most recent result
or all results, based on a parameter to the query. I don't seem to be able
to find a syntax that will allow me to do something like:

SELECT TOP [Enter how many things] things FROM MyTable ORDER BY thingNum;

Has anyone solved this before?

TIA;

Amy

 

[John Spencer]

You can't use a parameter to set the Top predicates value.

You can build the query's sql on the fly using VBA or you can use a ranking query

Select Top N records where N is variable ( a parameter)

One method that can be used is to create 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
The Hilltop Institute
University of Maryland Baltimore County

 

[Amy Blankenship]

You can't use a parameter to set the Top predicates value.

You can build the query's sql on the fly using VBA or you can use a
ranking query

Select Top N records where N is variable ( a parameter)

One method that can be used is to create 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])

Cool, thanks. The query I was using this way was already a subquery, so I
wound up using IIF([Return All?], (SELECT myID FROM myTABLE), (SELECT
Max(MyID) FROM myTABLE));