"Optimize for N rows" command

M

MK

Anyone have a workaround for getting Access to return a limited number of rows which mee
the criteria, instead of all the rows

Thanks
 
K

Kevin Sprinkel

In Query Design view, change the Top Values property to
the number of rows you want.

HTH
Kevin Sprinkel
-----Original Message-----

Anyone have a workaround for getting Access to return a
Click to expand...
limited number of rows which meet
 
M

Michel Walsh

Hi,



You can try,
---------------------------------------------------------------------
SELECT a.f1, LAST(a.f2), LAST(a.f3), LAST(a.f4)

FROM myTable As a LEFT JOIN myTable As b
ON a.f1 < b.f1

GROUP BY a.f1

HAVING COUNT(b.f1) < [n]
----------------------------------------------------------------------


If you have no tie values, you can change the LEFT join for an inner join,
and all the < for <= .



If the ORDER BY was initially on many fields, such as:

ORDER BY f1, f2

then, change the ON clause to

ON a.f1 < b.f1 OR ( a.f1=b.f1 AND a.f2<b.f2 )

and add a.f2 in the GROUP BY list, remove its aggregate LAST in the SELECT
list.




Hoping it may help,
Vanderghast, Access MVP
 
Top