Using top clause in Access sub report

I'm making a report in Access 2003 that contains a sub report of related records. Within the sub report, I want the top two records only. When I add "TOP 2" to the sub report's query, it seems to select the top two records before it filters on the link fields. How do I get the top two records of only those records that apply to the corresponding link field? Thanks.

From stackoverflow

• I've got two suggestions:
  1) Pass your master field (on the parent form) to the query as a parameter (you could reference a field on the parent form directly as well)
  2) You could fake out rownumbers in Access and limit them to only rownum <= 2. E.g.,

  SELECT o1.order_number, o1.order_date,
      (SELECT COUNT(*) FROM orders AS o2
          WHERE o2.order_date <= o1.order_date) AS RowNum
      FROM
          orders AS o1
      ORDER BY o1.order_date
  

  (from http://groups.google.com/group/microsoft.public.access.queries/msg/ec562cbc51f03b6e?pli=1)
  However, this kind of query might return an read only record set, so it might not be appropriated if you needed to do the same thing on a Form instead of a Report.

  David-W-Fenton : I can't say if your solution will work, but in a report you certainly don't need to worry about returning a read-only recordset! :)

  CodeSlave : D.F. You're absolutely right. I was thinking in about Forms at the time.

  pro3carp3 : CodeSlave: Thank you for your input. I did a work-around where before I opened the report I put all of the relevant records (top 2 of each type) in a table and used that as the recordsource of the subreport. It is somewhat of hack, but does the job.

  Yarik : CodeSlave: the idea #2 is inventive, but it has two small problems: the query returns TWO OR MORE oldest orders among ALL ORDERS (not exactly two oldest orders for each customer). OTOH, the firs problem may be declared to be a useful feature. :-)

• The sample query below is supposed to return a pair of most recent orders for each customer (instead of all orders):

  select
      Order.ID,
      Order.Customer_ID,
      Order.PlacementDate
  from
      Order
  where
      Order.ID in 
          (
              select top 2
                  RecentOrder.ID
              from
                  Order as RecentOrder
              where
                  RecentOrder.Customer_ID = Order.Customer_ID
              order by
                  RecentOrder.PlacementDate Desc
          )
  

  A query like this could be used in your sub-report to avoid using a temporary table.

  CAVEAT EMPTOR: I did not test this sample query, and I don't know if this query would work for a report running against Jet database (we don't use Access to store data and we avoid Access reports like plague :-). But it should against SQL Server.

  I also don't know how well it would perform in your case. As usual, it depends. :-)

  BTW, speaking of performance and hacks. I would not consider usage of temporary table a hack. At worst, this trick can be considered as a more-complicated-than-necessary interface to the report. :-) And using such temporary table may actually be one of the good ways to improve performance. So, don't hurry writing it off. :-)