Access 2010 : Limiting records in a grouped report

Topic

New Reply

Hello, My first time here. I hope someone will be able to help me.

I am trying to limit the number of records returned in a grouped report. I just want X number of records returned in each group.

I did find this thread in this forum as it applied to access-97.
http://windowssecrets.com/forums/showthread//24262-Limit-number-of-records-in-report-(Access-97)

I was successful with the first part of the instruction and I was able to add a control that numbers the records in each group. However I was unable to limit the output, it still returned all the records.

Should these instructions also work in 2010 or is there an extra step I need to do ? I can not figure it out. If anyone could help it would be appreciated
Thank you

Reply | Quote

Replies

I might be able to help with a bit of VBA. If you are printing or doing a PrintPreview, here are the steps I see:

Define a module-level variable in the report. Put this line at the top of the report’s module:

Private mlngLines as Long

Then in the Format event of the relevant Group, put this line:

mlngLines = 0

This resets the number of lines printed to 0 each time a new group is created.

Finally, put this code in the detail section’s Format event:

mlngLines = mlngLines + 1
Cancel = mlngLines > 5

These lines of code will cancel the formatting of the detail section if five lines have already been formatted. Although the report will process all of the detail rows, only the first five will print on the report.

So, remember to open the report in Normal (printing) or PrintPreview and this should help you. This method becomes more problematic if you are opening the report in ReportView. Events do not fire the same in ReportView as they do in Normal and PrintPreview.

Jim

Reply | Quote

Thanks James,
I finally figured out all that code would only prevent it from printing all the lines.

What I really want to do is have a report that only loads the last 5 lines. The part that really interests me are the controls in the group headers. I want these controls (average, SDev etc…) to only take into account the last 5 records in each group.
That is my problem.

Is this a bigger design issue on my part or should this be quite easy to do in a report and I am missing the boat ?

Thank you

Reply | Quote

The only other solution I can think of at this point is to dump the entire contents of the report’s RecordSource into a temporary table. Open a properly-sorted Recordset against the table. Run code to loop through the table, deleting all the but five appropriate rows for each relevant group. Then open the report against the remainders in the table. All of this can be accomplished in the Report_Open event.

There are probably SQL gurus out there that can craft a SQL statement that will give you the proper set of records, but that is beyond my imagination at the moment.

Good luck to you.

Reply | Quote

You are changing the rules, you are wanting the last 5 now.

Would sorting in descending order and employing Jame’s solution work for you?

Reply | Quote

The last 5 records in each each group meaning the 5 most recent records. The TOP command limits the records in the underlying query before they are grouped, I need to limit the number of records after they are grouped. As Jame’s says “This method becomes more problematic if you are opening the report in ReportView. Events do not fire the same in ReportView as they do in Normal and PrintPreview.”

Reply | Quote

Have you tried adding an invisible control that has a control value of =1 then setting the Running Sum to Yes. You then test this in the Detail OnPrint section if >5 as James has shown you.

Reply | Quote

Yes Patt, I did do that and it works and this limits the records in the print preview. This will be useful at some point but printing is not my main concern at the moment, my main concern is seeing the header controls do their thing with the 5 most recent records per group. I am looking to find trends, I need to see if the short term (last 5, 12 or whatever amount) or records follow the historical longer term trend.

My first report shows all the records (grouped by track, surface, distance) and the header controls calculate (avg number of entries, avg split times whatever) I get numbers calculated for each group. I then have a second report that has the same structure but I only want it with X number of records. I will then be able to compare the control calculations for all my records versus the most recent. I do not want to create 35-40 queries that represent every grouping, I know there has to be a way.

I hope I am making sense in what I am trying to do.

Reply | Quote

What you could do is build a query on the fly and use that in the report.

Reply | Quote

Thanks James. You would think this should be easy.
I am exploring the subquery avenue
http://allenbrowne.com/subquery-01.html#TopN
Seems to describe what I need.
I’ll report back when I succeed.

Reply | Quote