Link to home
Start Free TrialLog in
Avatar of GeorgeAdrian
GeorgeAdrianFlag for Canada

asked on

How to limit number of records per group on report

I have a report that has works fine but gives me too much inof. Currently The reports groups everything the way I need it. Example



Etc. However each of these grouping are very long and I don't always need all of the items listed.

what I need to do is only have the top 3 items in each group appear on the page.

Does any one know how to do this?


Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Take a look at this property:

GroupInterval Property
See AlsoApplies ToExampleSpecificsYou can use the GroupInterval property with the GroupOn property to specify how records are grouped in a report. Read/write Long.

expression    Required. An expression that returns one of the objects in the Applies To list.

The GroupInterval property specifies an interval value that records are grouped by. This interval differs depending on the data type and GroupOn property setting of the field or expression you're grouping on. For example, you can set the GroupInterval property to 1 if you want to group records by the first character of a Text field, such as ProductName.

The GroupInterval property settings are Long values that depend on the field's data type and its GroupOn property setting. The default GroupInterval setting is 1.

You can set this property by using the Sorting And Grouping box, a macro, or Visual Basic.

You can set the GroupInterval property only in report Design view or in the Open event procedure of a report.

Here are examples of GroupInterval property settings for different field data types.

Field data type GroupOn setting GroupInterval setting
All Each value (Default) Set to 1.
Text Prefix characters Set to 3 for grouping by the first three characters in the field (for example, Chai, Chartreuse, and Chang would be grouped together).
Date/Time Week Set to 2 to return data in biweekly groups.
Date/Time Hour Set to 12 to return data in half-day groups.

Note  To set the GroupInterval property to a value other than its default setting (1), you must first set the GroupHeader or GroupFooter property or both to Yes for the selected field or expression.

The following example sets the SortOrder and grouping properties for the first group level in the Products By Category report to create an alphabetical list of products.

Private Sub Report_Open(Cancel As Integer)
    ' Set SortOrder property to ascending order.
    Me.GroupLevel(0).SortOrder = False
    ' Set GroupOn property.
    Me.GroupLevel(0).GroupOn = 1
    ' Set GroupInterval property to 1.
    Me.GroupLevel(0).GroupInterval = 1
    ' Set KeepTogether property to With First Detail.
    Me.GroupLevel(0).KeepTogether = 2
End Sub

Given the table has a primary key named pk and a group field named gpfld:

SELECT a.* FROM myTable WHERE IN (SELECT TOP 3 FROM myTable WHERE b.gpfld = a.gpfld  ORDER BY gpfld, pk DESC);
Avatar of GeorgeAdrian



I thought of this approach, however where do I put the query, In the Open Form Event, or does the query need to be run everytime there is a new Group Level?

Please advise and I will try.

BTW - I assume that the "b" in refers to an Alias for "a"

How Would I use this ir I just want 2 items in each Group. The groupings are done by an ID field. If i understand this correctly this will only return something based on the first x number of charactors? I only want the Top 2 items of each grouping to appear on the report.


I am pretty sure you would have to write some code using maybe the NextRecord property, etc., unless you can jury rig it with the Grouping Interval some way.  Maybe have a text box with a Running Sum (Control Source =1) over the group, get that value in code ... the do the Next Record ... I don't  know.

For reference:

NextRecord Property
See AlsoApplies ToExampleSpecificsThe NextRecord property specifies whether a section should advance to the next record. Read/write Boolean.

expression    Required. An expression that returns one of the objects in the Applies To list.

The NextRecord property uses the following settings.

Setting Description
True (Default) The section advances to the next record.
False The section doesn't advance to the next record.

To set this property, specify a macro or event procedure for a section's OnFormat property.

Microsoft Access sets this property to True before each section's Format event.

The following example sets the NextRecord property to False for a given report.

Public Sub ChangeNextRecord(r As Report)
    r.NextRecord = False
End Sub

Avatar of GRayL
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial