[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


How to Create Multiple Detail Sections on Reports

Published on
39,762 Points
4 Endorsements
Last Modified:
Community Pick
Sometimes, when developing a report in Access, one might wish for more than one single Detail Section. Perhaps 22 inches is just not enough, perhaps conflicting settings should be applied to different areas of the section, perhaps a section break could serve as a conditional page break...

There is a simple trick to obtain as many Detail Sections as needed. Well, there is a limit — isn't there always? — of ten user-defined sections on any report (and 200" total height). There is also a prerequisite: the rows must have a unique identifier.

If there is an “identity” field, or a combination of fields that serve as unique key, then you can create a group on that field or fields. Since the key will change for every record, the “group” will contain only a single record, and thus both the group header and the group footer will behave just like the detail section...

For example, if you are designing a complex financial report for your customers, based on the Customers Table, having the field ID as key field, you simply create a group on that field:

Open the report in design view,
activate the “Sorting and Grouping” sheet,
select the field ID as “Field/Expression”,
(if you have more than one key field, select them one by one on succesive rows),
set “Group Header: yes” (on the last field of multi-field keys),
optionally set “Group Footer: yes” for a total of three Detail Sections.
header and footer on ID fieldTip: If you need to sort the records (e.g. by customer name or by country), the sort order must be given at the very top, before the key fields used to create multiple detail sections. That is because in “Sorting and Grouping”, the “sorting” portion cannot be turned off. Naturally, the same is true for real grouping you might need also: they must be created “before”.

When you open the properties of the sections, you can now adjust each section differently, typically: keep together, can grow and can shrink. A page break can occur between two sections that are “kept together” on a page; this implements a soft page break. Conversely, if you need the sections to always print on a single page, there is a the option “Keep Together: Whole Group” on the “Sorting and Grouping” sheet.

If you control formatting and layout though Visual Basic event handlers, it can be very useful to have distinct “on format” events for each section. A section can also be made visible or invisible with a single line of code, which is more efficient than doing the same for two dozen correlated controls.

Tip: If you need to refer to the sections through code, it is good practice to rename them, for example as secDetail1, secDetail2, and secDetail3. This will make the code easier to read and hints at the fact that they are all just like detail sections.

Theoretically, the same method can be used to obtain several group headers for the same group, by simply repeating the grouping field(s) or expression(s), but that is a rather unlikely requirement.

I have used this trick often, sometimes simply to avoid a sub-report and other times as basis for more convoluted advanced tricks like vertical centring. The day you need it, I hope you will be glad to know how to do it.

Markus G Fischer

This article is part of the EE-book unrestricted Access.
1 Comment

Expert Comment

by:kamal dev
man can any body tell how to connect access 2016 v july 2017 with any external reporting tool. as it has limitations creating bills and invoices.

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month