Hiding Duplicate Values in Continuous Forms via Subqueries

This article is Part 2 of my "Hiding Duplicate Values" Mini-Series.

To follow along with the screenshots and examples, download the sample data from here: Sample Data for the "Hiding Duplicate Values" Series.

Starting Point

Unlike reports, controls on Access forms do not have a "Hide Duplicates" property.

The simplest way to hide duplicate values on a continuous form is by way of the underlying record source.  We'll start by creating a record source that includes all the columns and tables we need, before looking at a couple of ways to hide the individual duplicate values.

Basic Query

Here's the basic query:

Our goal is to generate a query with the same columns, but where the duplicate values are hidden.  Here's the above query with the values we want to hide shown in gray:

Method 1: Nested Subqueries

Colin Riddington (aka, IslaDogs) ably demonstrates this technique in his article Hide Duplicate Column Values in Continuous Forms, (which, incidentally, served as inspiration for this series).

In Colin's example, all the data was in a single table (tblProcInfo).  More commonly, though, duplicate data will appear as a result of joining tables with one-to-many relationships, as we are doing here with Customer, Invoice, and InvItem.  

Another difference from Colin's example is that we have two levels of grouping instead of one: FirstName/LastName and InvoiceDate/DueDate.  Notice in the sample data above that Cosmo Kramer has two invoices: one dated 2/15/2024 and the other dated 2/25/2024.

Creating the Group By Queries

The first step is to create one or more GROUP BY queries that will serve as subqueries for the form's record source.

MinItemByInvoice Query

We start by creating a group by (aka, "Totals") query for the InvItem table that will return the minimum InvItemID field value, along with the InvoiceDate and DueDate fields from the joined Invoice table:

MinItemByCustomer Query

Each group header's subquery must include the primary key for the detail-level table.  For our example, that would be the InvItem.InvItemID field.

For multi-level grouping, each higher level group builds off of the lower level's GROUP BY query.  

We start the query by adding the MinItemByInvoice query from above, and then joining the Invoice and Customer tables to it.  As before, this is a "Totals" query.

Notice that for readability, I've aliased the Min(MinOfInvItemID) field to MinItemID to avoid the "MinOfMinOfInvItemID" that gets auto-generated.

I'm grouping by the CustomerID, FirstName, and LastName fields:

There are two key characteristics of the returned results:

• There is one row for each Customer
• The results include the primary key value for our detail-level table, InvItem

Creating the Form Record Source

Now that we have our subqueries, we can build the form record source.

First, we start by creating a copy of the StartingPoint query and rename it HideDupesViaSubqueries.

Next, we add the MinItemByCustomer and MinItemByInvoice subqueries to the existing query via LEFT JOINs on the InvItem.InvItemID field:

Note that at this point, the query results are exactly the same as before:

The final step is to change the table names for our "group headers" as follows:

• FirstName: from Customer to MinItemByCustomer
• LastName: from Customer to MinItemByCustomer
• InvoiceDate: from Invoice to MinItemByInvoice
• DueDate: from Invoice to MinItemByInvoice

And that provides us with the final query that we can use as a RecordSource for our continuous form: