Hiding Duplicate Values in Reports

This article is Part 1 of my "Hiding Duplicate Values" Mini-Series.

To follow along with the screenshots and examples, download the sample data from here: Sample Data for the "Hiding Duplicate Values" Series.

Traditional Grouping

The first report shows the traditional approach of using Grouping and Sorting to create group headers where we display the table data for the parent table(s) and a detail section where we show the records from the detail table.

We'll use the common example of customer invoices, represented by the following three tables and associated values:

• Customer: First Name, Last Name
• Invoice: Invoice Date, Due Date
• InvItem: Description, Item Quantity, Unit Price, Total

Here's what an Invoice Report using traditional grouping looks like:

There are three data sections in this report:

• A CustomerID group header with the first and last names of our customers
• An InvoiceID group header with the invoice and due dates for each invoice
• A Detail section with invoice item details for each invoice

Design View

Here's the design view of the report:

The highlighted sections above correspond with the highlighted grouping and sorting levels shown below:

A Note About Sorting in Reports

The ORDER BY clause of the report's RecordSource is completely ignored when sorting the report.

If you want to sort the items of your report, you must use the "Group & Sort" panel of the "Report Design" ribbon tab:

Another Note About Grouping and Sorting in Reports

Note that if you Group on a field, you also automatically sort by that field.

For this simple example, I didn't bother adding a sort order that would be obvious to a user.  In practical terms, the above group/sort settings will result in a report that is sorted:

• Firstly, in the order Customers were added to the database (oldest to newest)
• Then, in the order Invoices were added to the Customers' accounts (oldest to newest)
• Finally, in the order Invoice Items were added to the Invoices (oldest to newest)

In a real application, I generally prefer to use a sort order that can be seen by the user so that they can make better sense of the report.

To do that, I would add sorting levels before the groups to which they apply.  I would still group on the unique values, though, so as to avoid accidentally grouping unrelated items.  This is unlikely to happen with Customer Names, but grouping by Invoice Date could easily result in multiple invoices appearing as one on the report.

Here's what that would look like:

Here's how the report looks with the additional sorting levels added:

Notice that Cosmo Kramer is now listed first by virtue of being first alphabetically.

Hiding Duplicate Values

Another way to present this same data is with a single detail section:

This is a more compact format, but now it's much tougher to identify the individual invoices.

However, if we hide the duplicate values, we get a report that represents the best of both worlds: a compact format with clear indications of where each customer and invoice starts and ends:

This feature requires NO CODE!  You just have to know where to look.

Setting the "Hide Duplicates" Property

The property name is "Hide Duplicates" and you set it on the Format tab of the control's Property Sheet.

Official Documentation

Here's the Microsoft documentation for the Hide Duplicates property:

Use the HideDuplicates property to hide a control on a report when its value is the same as in the preceding record.

The HideDuplicates property applies only to controls (check box, combo box, list box, option button, option group, text box, toggle button) on a report.

The HideDuplicates property uses the following settings:

• True: If the value of a control or the data it contains is the same as in the preceding record, the control is hidden.
• False: (Default) The control is visible regardless of the value in the preceding record.

Use the HideDuplicates property to create a grouped report by using only the detail section rather than a group header and the detail section.

Additional Reading

Microsoft Access tips: Hide duplicates selectively on a report

How to design an Access report where values are shown or hidden only when another field changes.