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.

Assume 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 Pivot Table.

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 as you expected. Some people suggest to use isVisible Property in Department Text Box Expression 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 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, which is slightly different from the conventional 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

Leave a Reply

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