Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Limiting the number of records in a report

Discussion in 'Information Technology' started by DSharbaugh, Jul 28, 2006.

  1. DSharbaugh

    DSharbaugh
    Expand Collapse
    Guest

    How do I limit the number of detail records in an Access report? The report
    is sorted by several fields, but I want only 50 records for the first sort
    field.
     
  2. Loading...

    Similar Threads Forum Date
    Shabad Numbering System New to Sikhism Dec 1, 2015
    USA A Heart Attack And The Numbers Breaking News Aug 2, 2014
    Pacific Borneo: Sikhs small in number, strong in presence Breaking News Nov 11, 2013
    World Shrinking Numbers and Growing Persecution Threaten Sikhs and Hindus in Afghanistan Breaking News Nov 7, 2013
    SciTech Mammals pick offspring’s sex to maximize number of grandchildren, study shows Breaking News Jul 11, 2013

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 6 Jun 2006 20:20:01 -0700, DSharbaugh
    <DSharbaugh@discussions.microsoft.com> wrote:

    >How do I limit the number of detail records in an Access report? The report
    >is sorted by several fields, but I want only 50 records for the first sort
    >field.


    Base the Report on a query and set the query's Top Values property
    (right mouseclick the grey background of the tables and select
    Properties) to 50.

    John W. Vinson[MVP]
     
  4. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Go to the design view of the report

    turn on the properties window
    (from the menu --> View, Properties)

    select the report itself
    (click in the upper left where the rulers intersect)

    the titlebar of the properties window will say "Form"

    in the properties window, click on the DATA tab

    click in the RecordSource property

    click on the Builder (...) button to the right

    now, you are on a screen that looks like a query

    turn on the properties
    (View, properties from the menu)

    click in a gray area that is not a fieldlist or a column

    the titlebar of the properties window will say "Query
    Properties"

    click in the TOP VALUES property and enter 50
    (you can enter a specif number or a percentage)

    from the File menu, choose close
    and then Yes to update the SQL

    save your form

    Voila!

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    DSharbaugh wrote:
    > How do I limit the number of detail records in an Access report? The report
    > is sorted by several fields, but I want only 50 records for the first sort
    > field.
     
  5. DSharbaugh

    DSharbaugh
    Expand Collapse
    Guest

    Thanks, but I should have given more detial on my question... The query on
    which the report is based is used for multiple other reports, which should
    not be limited, so I cannot limit records at the query level. Also, I want
    to limt the records to a set number in each group, not a total for the
    report. One of the categories the report is grouped on is "urgency value".
    I want only the top 50 records in each urgency value group to appear on the
    report.

    Is it possible to do this at the report level, or do I have to create a
    separate query and report for each urgency group?

    Many thanks,

    dsharbaugh

    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Go to the design view of the report
    >
    > turn on the properties window
    > (from the menu --> View, Properties)
    >
    > select the report itself
    > (click in the upper left where the rulers intersect)
    >
    > the titlebar of the properties window will say "Form"
    >
    > in the properties window, click on the DATA tab
    >
    > click in the RecordSource property
    >
    > click on the Builder (...) button to the right
    >
    > now, you are on a screen that looks like a query
    >
    > turn on the properties
    > (View, properties from the menu)
    >
    > click in a gray area that is not a fieldlist or a column
    >
    > the titlebar of the properties window will say "Query
    > Properties"
    >
    > click in the TOP VALUES property and enter 50
    > (you can enter a specif number or a percentage)
    >
    > from the File menu, choose close
    > and then Yes to update the SQL
    >
    > save your form
    >
    > Voila!
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > DSharbaugh wrote:
    > > How do I limit the number of detail records in an Access report? The report
    > > is sorted by several fields, but I want only 50 records for the first sort
    > > field.

    >
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi

    put this control on your report in the detail section:

    then, in the Detail OnFormat event -->

    textbox:
    Name --> CountRows
    ControlSource --> =1
    RunningSum --> Overall

    '~~~~~~~~~~~~
    Private Sub Detail_Format( _
    Cancel As Integer, _
    FormatCount As Integer)

    If Me.CountRows > 50 Then
    Me.Detail.Visible = False
    End If
    End Sub
    '~~~~~~~~~~~~~

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    DSharbaugh wrote:
    > Thanks, but I should have given more detial on my question... The query on
    > which the report is based is used for multiple other reports, which should
    > not be limited, so I cannot limit records at the query level. Also, I want
    > to limt the records to a set number in each group, not a total for the
    > report. One of the categories the report is grouped on is "urgency value".
    > I want only the top 50 records in each urgency value group to appear on the
    > report.
    >
    > Is it possible to do this at the report level, or do I have to create a
    > separate query and report for each urgency group?
    >
    > Many thanks,
    >
    > dsharbaugh
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>Go to the design view of the report
    >>
    >>turn on the properties window
    >>(from the menu --> View, Properties)
    >>
    >>select the report itself
    >>(click in the upper left where the rulers intersect)
    >>
    >>the titlebar of the properties window will say "Form"
    >>
    >>in the properties window, click on the DATA tab
    >>
    >>click in the RecordSource property
    >>
    >>click on the Builder (...) button to the right
    >>
    >>now, you are on a screen that looks like a query
    >>
    >>turn on the properties
    >>(View, properties from the menu)
    >>
    >>click in a gray area that is not a fieldlist or a column
    >>
    >>the titlebar of the properties window will say "Query
    >>Properties"
    >>
    >>click in the TOP VALUES property and enter 50
    >>(you can enter a specif number or a percentage)
    >>
    >>from the File menu, choose close
    >>and then Yes to update the SQL
    >>
    >>save your form
    >>
    >>Voila!
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>DSharbaugh wrote:
    >>
    >>>How do I limit the number of detail records in an Access report? The report
    >>>is sorted by several fields, but I want only 50 records for the first sort
    >>>field.

    >>
     

Share This Page