Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit number of records in report (Access 97)

    Is there any way that I can limit the number of records in a report group to a maximum number? I am using a sales file and I have the report grouped by item number and want to list in my report no more than the last 5 sales transactions for each item. Using the date range doesn't work very well as some items may have been sold only three times in the past three years and others may have been sold 100 times in the past three years.
    Any help would be greatly appreciated.

    Bob Gott

  2. 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
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit number of records in report (Access 97)

    Hi--

    I haven't actually done this myself, but there is a query property for TopValues. You can set the property in the design view of the query, then base your report on the query. Check it out in the help.

    You can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.

    Note The TopValues property applies only to append, make-table, and select queries.

    Setting

    The TopValues property setting is an Integer value representing the exact number of values to return or a number followed by a percent sign (%) representing the percentage of records to return. For example, to return the top 10 values, set the TopValues property to 10; to return the top 10 percent of values, set the TopValues property to 10%.


    THere's also an example in there.

    Good luck!

    Cecilia :-)

  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Limit number of records in report (Access 97)

    This is one way - undoubtedly there are others:

    Sort the report on transaction date - descending. In the Sorting and Grouping window, this must come below the Item number.

    Put an unbound text box in the detail section.
    Make it invisible.
    Set the Control Source property to =1
    Set the Running Sum property to Groups (don't know the exact wording in English).
    Name it Counter.

    Now select the Detail section.
    Create code for the OnFormat event:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [Counter] > 5 Then Cancel = True
    End Sub

    The value of Counter will be increased for each record. Once it has passed 5, the detail section will be canceled. With each new Item number, Counter is reset to 1.

  5. #4
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit number of records in report (Access 97)

    Thank you very much. This did exactly what I wanted and I was able to cut the report in half.
    Again, thank you very much,

    Bob Gott

  6. #5
    New Lounger
    Join Date
    May 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit number of records in report (Access 97)

    I tried this but it only worked on the whole record set. I could not find anywhere where I could use this at the detail section level of a report.
    Thank you very much for your quick respnse.

    Bob Gott

Posting Permissions

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