Hiding Duplicate Values in Microsoft Access Forms and Reports

Mike Wolfe -

This mini-series will cover basic through advanced use cases for hiding duplicate values in Access forms and reports.

When I talk about "hiding duplicate values," I'm talking about the specific situation where you have detail records in a one-to-many relationship with one or more parent tables, and you want to show the parent table values for the first record but hide the parent table values for subsequent records.

This is easiest to demonstrate with a couple of example reports.  

Traditional Grouping

The first report shows the traditional approach of using Grouping and Sorting to create group headers where we display the table data for the parent table(s) and a detail section where we show the records from the detail table.

We'll use the common example of customer invoices, represented by the following three tables and associated values:

  • Customer: First Name, Last Name
  • Invoice: Invoice Date, Due Date
  • InvItem: Description, Item Quantity, Unit Price, Total

Here's what an Invoice Report using traditional grouping looks like:

There are three data sections in this report:

  • A CustomerID group header with the first and last names of our customers
  • An InvoiceID group header with the invoice and due dates for each invoice
  • A Detail section with invoice item details for each invoice

Hiding Duplicate Values

Another way to present this same data is with a single detail section:

This is a more compact format, but now it's much tougher to identify the individual invoices.  

However, if we hide the duplicate values, we get a report that represents the best of both worlds: a compact format with clear indications of where each customer and invoice starts and ends:

Article Table of Contents

This series will be broken down into the following articles:

  • Part 0: Sample Data for the "Hiding Duplicate Values" Series
  • Part 1: Hiding Duplicate Values in Reports
  • Part 2: Hiding Duplicate Values in Continuous Forms via Subqueries
  • Part 3: Hiding Duplicate Values in Continuous Forms via Non-Equal Left Joins
  • Part 4: Use DCount() to Make Editable Continuous Forms with Hidden Duplicate Values
  • Part 5: Use Custom Functions to Hide Multi-Level Group Headers on Editable Continuous Forms
  • Part 6: Use Caching to Speed Up Editable Forms with Hidden Duplicate Values
  • Part 7: Invalidating the Cache on the Hidden Duplicate Values Form
  • Part 8: Triggering Cache Invalidation on the Hidden Duplicate Values Form
  • Part 9: Forcing a Repaint of Editable Forms with Hidden Duplicate Values

Check back as we add links to the articles as they are written.

NOTE: This series is inspired by Colin Riddington's recent post, Hide Duplicate Column Values in Continuous Forms.

UPDATE [2024-02-19]: Split the original Part 2 ("Hiding Duplicate Values in Continuous Forms") into two parts: a subqueries approach and a non-equal left joins approach.  Also, added links to Parts 0, 1, and 2.

UPDATE [2024-02-20]: Added link to Part 3 and updated article tags.

UPDATE [2024-02-21]: Split the previous Part 4 ("Making Editable Continuous Forms with Hidden Duplicate Values") into two parts: a DCount() approach and a custom functions approach.  Also, added link to part 4.

UPDATE [2024-02-24]: Added links to Parts 5 and 6.

UPDATE [2024-02-26]: Introduced a new part 7 and part 8, renaming the previous part 7 to part 9.  Added link to the new part 7.