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.

access_report_hide_data

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)

access_report_reset_page_number_09

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

access_report_hide_data_02

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.

access_report_hide_data_03

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.

access_report_hide_data_04

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.

access_report_hide_data_05

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Access Report Hide Duplicates Property

  1. Abbott Cheung says:

    For example, =IIF(Division.isVisible,Department,Null)
    the above formula is not working for me, why?
    please help, thanks in advance.

    1. Wyman W Wyman W says:

      Hi Abbott, I know this formula looks strange but it worked for me in 2015 but its not working for me today in 2017, not sure if it is because of the Access version. Anyway,forget about the formula because it cannot perfectly remove the duplicate, please use the Group at the bottom instead

Leave a Reply

Your email address will not be published. Required fields are marked *