Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2010 : Limiting records in a grouped report

    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/sho...rt-(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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Mar 2013
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solve it with VBA

    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

  4. #3
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,522
    Thanks
    0
    Thanked 22 Times in 22 Posts
    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?

  6. #5
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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."

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,522
    Thanks
    0
    Thanked 22 Times in 22 Posts
    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.

  8. #7
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,522
    Thanks
    0
    Thanked 22 Times in 22 Posts
    What you could do is build a query on the fly and use that in the report.

  10. #9
    New Lounger
    Join Date
    Mar 2013
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool Another VBA solution

    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.

  11. #10
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •