The Microsoft Access Solution
If you're working with MS Access as your front end, you don't need code to limit the number of records in a grouped report. Report objects can do the work for you. Use a main report to identify the group and a subreport to limit the grouped records. It's an easy trick that works well.
First, you retrieve (link to) the SQL Server data. Then, base a Totals query on the SQL Server data, and include only the field by which you're grouping the report. Base the main report on this Totals query. A second query is the basis for the subreport, which displays the data you need to see. By limiting this query to the number of records by which you want to limit each group, you get the effect of limiting a group without actually doing so. Technically, the limit belongs to the subreport, not the group.
Whether you're using MS Access to link to SQL Server data or native tables, the following technique works:
- Create a new query on the table that contains the data that you want to limit.
- Drag the field by which you're grouping the records to the design grid. This query will contain just this one field (groupfield from the SQL Server example in the previous section).
- In the MS Access Query Design window, choose Totals from the View menu. MS Access will add a GROUP BY aggregate to the field's Total cell, as shown in Figure 5. You'll base the main report on this query.
|Figure 5. GROUP BY Aggregate Added to the Field's Total Cell: Let a Totals query specify the group.|
- Save the query (qryMainReport) and close it.
- Create a SELECT query and include all of the data you want to view in your report from the sources necessary, including the field by which you're grouping the report (see Step 2). Both queries must include the grouping field. You'll base the subreport on this query.
- In the grouping field's Criteria cell, enter the name of the main report and its one field (it doesn't exist yet, but that's okay) as shown in Figure 6. Be sure to enter a complete identifier in the following form: Reports!reportname!groupfield.
- Set this query's Top Values property to the number of records by which you're limiting each group. For instance, if you want to limit each group to two records, you'd enter the value 2 (see Figure 7). To access this property, choose Properties from the View menu. Although you're not going to sort these records in this exercise, if you did want to sort them, this is the right query for that: choose a Sort order from the Sort cell in the design grid.
Figure 6. Post List Page with Tags: Identify the subreport in this query.
Figure 7. Editing Tags for a Post: Use the query's Top Values property to limit the number of records the subreport retrieves.
- Save the second query (qrySubReport) and close it.
- Base a simple report on the first query (qryMainReport).
- With the main report in Design view, insert a Subform/Subreport control into the report's Detail section. Use Figure 8 as a guide.
- Double-click the subreport to open its Properties window and set the following properties, as shown in Figure 9:
- Source Object: Query.qrySubReport (should be set for you)
- Link Child Field: OrderID
- Link Master Field: OrderID
Figure 8. Subform/Subreport Control in the Report's Detail Section: Insert the subreport, which you based on the Top Values query.
Figure 9. Setting Properties in Subreport Properties Window: Link the two reports.
- Resize the subreport to be about the size of a single row.
- Save the report.
|Figure 10. Print Preview of the Report: The report seemingly limits the number of grouped records.|
In Print Preview, the report seemingly limits the number of grouped records (see Figure 10
). Despite the way it looks, this solution is simple and relies on the subreport's query to limit the records. This technique doesn't actually limit a grouped recordset or report. The one limitation is that you must change the query's Top Value
property to change the record limit.
Limiting the number of records in a group is one of those problems that seems simple until you start applying solutions. Regardless of which database you're using, this article has shown you that both SQL Server and MS Access can handle the task.