top 3 from subtable

[Guest]

I have (main table), tblAction with actionID, title, name. Another table
(child table), tblRemarks with remID, actionID, remDate, Remark

I would like create query/report to show all record from tblAction but show
only the top 3 remDate on each group actionID from the tblRemarks.

Thank you in advance.

eg. actionID title RemDate Remarks
1 www 1/31/07 remwww
1 xxx 3/2/07 remxxx
1 yyy 4/4/07 remyyy

2 zzz 2/4/07 remzzz
2 aaa 4/31/07 remaaa
2 bbb 5/2/07 rembbb
2 ccc 6/4/07 remccc


Results should be:
eg. actionID title RemDate Remarks
1 yyy 4/4/07 remwww
1 xxx 3/2/07 remxxx
1 www 1/31/07 remyyy

2 ccc 6/4/07 remzzz
2 bbb 5/2/07 remaaa
2 aaa 4/31/07 rembbb

 

[Allen Browne]

Create a query using tblRemarks, sorted by remDate Descending (i.e. most
recent first.) In the properties box in query design, set the query's Top
Values to 3.

Create a report bound to tblAction, with a subreport bound to the query.

If you want to do it in a query instead of a report, here's how:
http://allenbrowne.com/subquery-01.html#TopN

 

[Guest]

Thanks Allen.

Create a query using tblRemarks, sorted by remDate Descending (i.e. most
recent first.) In the properties box in query design, set the query's Top
Values to 3.

Create a report bound to tblAction, with a subreport bound to the query.

If you want to do it in a query instead of a report, here's how:
http://allenbrowne.com/subquery-01.html#TopN