CODEWITHSUNDEEP
ms-access-2007
Chasester
Chasester

Reputation: 704

Access 2007 Reports - limit results

I've got an Access 2007 database where I created a report showing projects (using tblProjects). I then created a sub-report showing project history (using tblProjectHistory). The sub-report is linked to the parent report by fldProjectID (tblProjectHistory) and projectID (tblProject).

The layout is such.

::Project information

--> project history information

++++++++

::Project information

--> project history information

(repeat)

The projects list out correctly, and any project history is also appropriately underneath the respective project. No overlap at all.

I'm wanting to limit the number of project history records in the sub-report to no more than 5 for each project. I've tried modifying the sub-report record source to have "TOP 5" in it. but then it ends up limiting the overall number of sub-report records - no matter if they are under one project or not.

Any idea how I could accomplish that mission? Thank you in advance for your time.

Upvotes: 0

Views: 405

Answers (1)

Rbit
Rbit

Reputation: 231

You were on the right track with TOP 5 but I can't think of a way you can do this using the tables themselves as a datasource for the subreport.

I would recommend using a query as the data source for the subreport based on the project ID in the report WHERE projectID = [reports]![reportname]![report project ID field]. You will then be able to group relevant records together by project and select the TOP 5 records for the subreport.

Upvotes: 1

Related Questions