Version 1.1           Approx 3.8 MB (zipped)                 First Published 25 Feb 2024


The Hide Duplicates feature in Access reports provides a convenient way of removing duplicate data in order to make the layout clearer to end users.
For example in this report, the duplicates in the first and third columns are hidden.


To create this effect, open the report in design view, select the required control and set Hide Duplicates = Yes in the property sheet.


However, my recent article, Edit Data in Continuous Forms with Hidden Duplicates , included screenshots of two reports using the Hide Duplicates feature where the results were far from satisfactory.


Hiding the duplicates highlighted in yellow on this report is unhelpful as it isn't possible to view all the data for each individual student at a glance.
The problem here is that there are successive students with identical last names, first names, gender, year and tutor group.

The same problem occurs on this report


In such cases, the Hide Duplicates feature is far too blunt an instrument as it lacks flexibility.

However, the reporting feature in Access is very powerful and it offers several ways of achieving the desired output. The example app provides 4 solutions.



Download

Click to download:           FixHideDuplicatesReports_v1.1      ACCDB file     Approx 3.8 MB (zipped)



Using the Example App

The example app opens to a startup form giving easy access to the forms and reports


Both the Student Medical Conditions and Student Contacts examples are included from the previous article.
However as the approach for each is identical, I will just focus on the Student Medical Conditions items.

The two forms from the previous article are included so you can see the type of layout we are trying to achieve. For example:


The first report is the standard Hide Duplicates report. This is the design view showing the 3 sort fields

NOTE:
Reports IGNORE any sorting and grouping in the record source.
These MUST be set on the report itself either using the report wizard (if used) or in design view as shown above


In print preview, the 'missing data' is obvious




Fixing the Reports

1. Use the record source from Form 1

As the first form has exactly the layout we need, you can use the same record source for your report.
In this case, the record source is the query qryDMinStudentMedicalConditions . The query SQL is:

SELECT ConditionID, DMin("ConditionID","qryMedicalConditions","PupilID = '" & [qryMedicalConditions].[PupilID] & "'") AS FirstConditionID,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[PupilID],"") AS PupilID,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Surname],"") AS LastName,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Forename],"") AS FirstName,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Gender],"") AS Gender,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[DateOfBirth],"") AS DateOfBirth,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[YearGroup],"") AS YearGroup,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[TutorGroup],"") AS TutorGroup, Surname, Forename, Description
FROM qryMedicalConditions
ORDER BY Surname, Forename, Description;

The report design is has the same sorting details. Note that Hide Duplicates is set to No. This is also the case for all the remaining reports.


The report layout in print preview is exactly what we want. However, for a much larger datasets, the multiple use of IIf statements may make the report slow to open



2. Use the record source from Form 2

Exactly the same comments apply as for the previous report but this is faster to load
The record source uses query qryGroupedStudentMedicalConditions. The query SQL is:

SELECT qryMedicalConditions.ConditionID, qryFirstStudentMedicalConditions.PupilID,
      qryFirstStudentMedicalConditions.Surname AS LastName, qryFirstStudentMedicalConditions.Forename AS FirstName,
      qryFirstStudentMedicalConditions.Gender, qryFirstStudentMedicalConditions.DateOfBirth,
      qryFirstStudentMedicalConditions.YearGroup, qryFirstStudentMedicalConditions.TutorGroup,
      qryMedicalConditions.Description, qryMedicalConditions.Surname, qryMedicalConditions.Forename
FROM qryMedicalConditions LEFT JOIN qryFirstStudentMedicalConditions
      ON qryMedicalConditions.ConditionID = qryFirstStudentMedicalConditions.MinOfConditionID
ORDER BY qryMedicalConditions.Surname, qryMedicalConditions.Forename, qryMedicalConditions.Description;

The report design is identical to the previous report as is the layout in print preview
The only difference is that it loads faster as there are no IIf statements



3. Use conditional formatting

This example uses the base query qryMedicalConditions with the DMin function used to manage the grouping. The query SQL is:

SELECT Students.PupilID, Students.Surname, Students.Forename, Students.Gender, Students.DateOfBirth,
      Students.YearGroup, Students.TutorGroup, StudentMedicalConditions.ConditionID, StudentMedicalConditions.Description,
      DMin("ConditionID","StudentMedicalConditions","PupilID = '" & [StudentMedicalConditions].[PupilID] & "'") AS MinConditionID
FROM Students INNER JOIN StudentMedicalConditions ON Students.PupilID = StudentMedicalConditions.PupilID
WHERE (((StudentMedicalConditions.Description) Is Not Null))
ORDER BY Students.Surname, Students.Forename;

Both the ConditionID primary key field and the calculated MinConditionID field are required but are hidden on the report.


Using conditional formatting, hide the duplicates by setting the forecolor property equal to the backcolor for each of the first 6 fields when ConditionID<>MinConditionID


One problem with this approach is that you cannot easily use alternate row colors

4. Use a stepped (or grouped) report

This uses exactly the same record source as the previous report. However, this time the report is grouped by the PupilID field.
Doing this creates a new report section called PupilID Header and the PupilID field is automatically moved into that section.


The other 5 student fields are than dragged into the PupilID Header section. I have also added a backcolor to the section so it stands out. The result is:


The same effect can be created during report creation using the wizard. You can also specify grouping intervals:


This method is very powerful and is often the simplest to create.
It is particularly useful as you can have up to 10 levels of sorting / grouping on your reports. See Access specifications and limits

The screenshot below shows the design view of a report with 3 grouping levels. One has a footer section, as well as a header, and which is used to display a dividing line


This results in the following layout which is designed to be similar in appearance to the Object Dependencies feature available from the Database Tools ribbon.


However, unlike the built-in Access feature, this report shows dependency info for all database objects.



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 25 Feb 2024

Return to Example Databases Page

Return to Top