By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Limiting Report Output

P: 1
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: AssignmentNumber (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.
Mar 2 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
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 AssignmentNumber, 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_Data and a query "select top 4 * from tblNotes" joining on the AssignmentNumber.

Nic;o)
Mar 3 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
Simply registering interest.
Mar 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.