Access Report Hide Duplicates Property

This Access tutorial explains the Hide Duplicates property in Access Report.

Access Report Hide Duplicates

Access Field has a Property called “Hide Duplicates”, which removes all consecutive duplicates in a column. This is similar to the Grouping of Pivot Table in Excel.

Suppose we have the following Report.

Switch to Design View, click on Division text box in Detail section.

In the Property Sheet, find the Hide Duplicates Property, set to Yes (default is No)

Now switch to Print Preview, you will find Division duplicates are removed.

If we add one more HR data at the bottom, you will see HR at the bottom is not removed, it is because the HR data are not consecutively repeated.

Therefore it is very important that you have to sort the data first before applying Hide Duplicates.

Using the above result, also change Hide Duplicates property of Department Text Box to Yes. We assume that Department is a branch of Division with many to one relationship.

As you can see in the below result, some Departments are unexpectedly missing. The Admin Department of HR and IT are gone. This is because Hide Duplicates property would not consider the relationship between Division and Department, unlike Excel Pivot Table grouping.

Conclusion of Access Hide Duplicates Property

Hide Duplicates Property is a very rough tool to remove duplicates, it may not provide result you expected. Some people suggest to use isVisible Property on Department Text Box to check the visibility of Division Text Box in order to avoid Null value in the first Department value of each Division.

For example,  =IIF(Division.isVisible,Department,Null)

This solution only works if you only have one Department for each Division, this still has not resolved the issue of the above example to simulate the tabular format of Excel Pivot Table.

The only solution to remove duplicates in Access Report is to use Group at the bottom of Design View to tell Excel the level of grouping.

As you group Division and Department as above, you will be using Group Header/Footer Sections to show unique value, however the layout is slightly different from the conventional Excel tabular format.

Outbound References

https://support.office.com/en-us/article/Find-hide-or-eliminate-duplicate-data-3CC805A2-2A13-4439-B0D3-6B23C7D60FBB