Data Management

Hide duplicate values in a grouped Access report

Hiding duplicates values in a report might have unexpected consequences, but there's an easy fix! Susan Harkins shows you how.

Access reports turn data into information - something meaningful to the reader. Accurate data is crucial, but readability is also an important consideration. Fortunately, there are several tricks for making reports more viewer-friendly. One such trick is to hide duplicate values, but when you do, be careful as hiding duplicates can be a wee bit too much of a good thing! (This technique applies to Access 2003 and earlier.)

The report below could make good use of hiding duplicates. As is, the report displays the order value for each item in the order. Since several items comprise some orders, the report would be easier to read if the report printed each order value just once for each order.

I've based this example on the Order Details Extended query in Northwind, the sample database that comes with Access.

To hide duplicate order id values in the example report, you'd do the following:

  1. With the report in Design view, double-click the OrderID control to launch the properties sheet.
  2. Set the Hide Duplicates property to Yes.

Displaying the order value only once per order makes good sense. The reader doesn't have to second guess—with a glance, the reader can see where the order begins and ends. That's because the order value is the one value in the one-to-many relationship between the orders and the products that comprise each order. That won't always be the result; when you hide duplicates on the many side, you can get unexpected results. For instance, look what happens when you hide duplicates for the product name. (Logically, you probably wouldn't hide the product names because they wouldn't repeat within a given order, but for the sake of the example, please play along because they represent the many side of a one-to-many relationship.)

If you hide duplicates in the product name field, the report doesn't display the product name for the first item in order 10251. That would be a problem for most of us. If you check the underlying query, you'll find that the product is Louisiana Fiery Hot Pepper Sauce—the same as the last item in the preceding order. The Hide Duplicates property doesn't know the difference between order 10250 and 10251.

You might be asking yourself just how helpful this property is if it can't discern between groups. The truth is, it can and you don't need a complex formula or macro. To hide duplicates for a many value within groups, group the report, as follows:

  1. With the report in Design view, click Sorting and Grouping on the Report Design toolbar.
  2. Choose OrderID from the first Field/Expression dropdown. It will automatically set the sort order to Ascending for you.
  3. Now, if you were to view the report right now, the product values would still ignore the group. To force this change, choose Yes from the Group Header (or the Group Footer) dropdown.

Now, the Hide Duplicates property evaluates the ProductID values within each order (group). Opening the group header (or footer) has the added benefit of adding a bit of white space between groups, which is another attribute that makes a report a bit more readable. If you decide you don't want the extra white space, set the group header's Height to something small, such as .0009. Don't close it completely, as that would negate the fix.

This problem is all but eliminated in Access 2007 and 2010. In Design or Layout view, right-click the control displaying duplicates and choose Group On field.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox