> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Hiding Duplicate Values in Continuous Forms via Subqueries
- URL: https://nolongerset.com/hiding-dupes-via-subqueries/
- Published: 2024-02-19T16:02:10.000Z
- Updated: 2026-05-08T12:38:44.000Z
- Description: In this installment, we use GROUP BY subqueries to generate a read-only record source for a continuous form that hides duplicate header values.
- Author: Mike Wolfe
- Tags: Hiding Dupe Values, #Import 2026-05-20 02:59

*This article is Part 2 of my ["Hiding Duplicate Values" Mini-Series](https://nolongerset.com/hiding-duplicate-values/)*.

*To follow along with the screenshots and examples, download the sample data from here:* [*Sample Data for the "Hiding Duplicate Values" Series*](https://nolongerset.com/sample-data-dupe-values/).

---

## Starting Point

[Unlike reports](https://nolongerset.com/hide-dupe-values-in-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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-31.png)

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-32.png)

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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-33.png)

## Method 1: Nested Subqueries

Colin Riddington (aka, IslaDogs) ably demonstrates this technique in his article *[Hide Duplicate Column Values in Continuous Forms](https://isladogs.co.uk/hide-duplicate-column-values/index.html)*, (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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-50.png)

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-51.png)

#### 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-52.png)

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-53.png)

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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-47.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-39.png)

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*

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-49.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-48.png)