"Optimize for N rows" command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Thanks
 
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
 
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
 
Back
Top