Getting TOP values in SUBREPORT 1

[sohunter]

I want to show 5 most recent payments for each client (in a report that shows multiple clients) in a payment subreport.

When I put the TOP 5 SQL into the the databasource for the subreport, most clients have nothing to show in the payments subreport, because the TOP is literally filtering the top 5 from qryPayments before it filters on the ClientID.

BTW - my "Link Child/Master fields" setting is correct in subreport's control.

This must be a common need for people creating subreports, but I just can't seem to figure it out, or see anything on this forum...

 

[dhookom]

Add a text box to the detail section of the report where you want only 5 records displayed:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Then add code to the On Format event of this report section
Cancel = Me.txtCount>5


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sohunter]

This worked perfect. Thanks much!