Limiting Report Output

[tom p]

Happy Friday,

I have an access database. There are 2 main tables. tbl_Assignment_ Data has fields populated by a form that the user provides input to. The fields in the above table are : Unit (Not unique, selected by a dropdown), Status (Not unique, selected by a dropdown), AssignmentName (Not unique, user defined), AssignedTo (Not unique, user defined), StartDate (not unique, user defined), Desription(not unique, user defined), AssignmentNumbe (unique, user defined)

The second table is tbl_Followup: The fields are: AssignmentNumbe r (not unique, links to tbl_Assignment_ Data), FollowupDate (not unique, user defined), FollowupNotes (not unique, user defined).

There are multiple forms, one to input the assignment info, another to populate the assignment followup, and one to modify each.

I have 3 reports. Each report is sorted and grouped by the assignment info in tbl_Assignment_ Data . One report lists all followup, one lists only closed assignments, the third only lists open assignments. Each report lists all of the follow up information, sorted by ascending date.

Ex:

Report:

Assignment #1 info here

Date Followup Notes
---------------------------------------------------------------------------------------------------------------
1/1/2007 info
2/5/2007 info

Assignment #2 info here

Date Followup Notes
---------------------------------------------------------------------------------------------------------------
1/15/2007 info
3/5/2007 info

I need the capacity to create a report that will list the report information on the top, like the 3 already created. However, I need to be able to limit the amount of followup notes to the most recent 4, or a user defined number. The problem I'm having is all of the followup is in one table for instance:

Assignment #1 mm/dd/yyyy notes
Assignment #1 mm/dd/yyyy notes
Assignment #1 mm/dd/yyyy notes
Assignment #2 mm/dd/yyy notes

etc.

So the "top" function in access wont work for me, because i need the X number most recent followup notes for each assignment.

Any help would be greatly appreciated.

[nico5038]

The TOP function will work, however you'll have to create a subquery or use subreports.

The subreport approach requires a query like:

select top 4 AssignmentNumbe r, DateFollowup, Notes from tblNotes

Now place on the Assignment report a new subreport and accept the proposed link from Access. This will force the subreport to show just the notes per Assignment.

Another approach would be to create a JOIN between tblAssignment_D ata and a query "select top 4 * from tblNotes" joining on the AssignmentNumbe r.

Nic;o)

[NeoPa]

Simply registering interest.