Others

Show a Series Name Only Once in a Microsoft Access Form

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Show a series name only on the first record in each group in an Access form.

Show a Series Name Only Once in a Microsoft Access Form
Product
Microsoft Access
Device & OS
not provided
Scenario
A form lists titles by series and is used to review or filter records and open another form.
Observed behavior
The series name repeats on every row; the desired display shows it only on the first row of each series.
Before you start

Identify the title table, series table, series ID, and field that determines title order. The example assumes the order field is non-null and unique within each series; replace the sample table and field names with your own.

Solution 1Recommended

Use a self-join query to show the first series name

Use this approach when the form can use a read-only query as its record source.

The query compares each title with titles in the same series that come before it or have the same order. Only the first title has a count of one, so the display field returns the series name for that row and an empty string for the others.

The grouped query is generally not editable. Filters applied to the form after the query runs can hide the first record and leave no series name visible for that group.

1
Open a new query

In Microsoft Access, select Create > Query Design, then switch to SQL View.

2
Enter the self-join query

Paste this example into SQL View and replace the bracketed table and field names as needed: SELECT T.[TitleID], T.[SeriesID], T.[TitleOrder], T.[TitleName], IIf(Count(*)=1, S.[SeriesName], '') AS [DisplaySeries] FROM [Titles] AS T, [Titles] AS P, [Series] AS S WHERE T.[SeriesID]=P.[SeriesID] AND P.[TitleOrder]<=T.[TitleOrder] AND T.[SeriesID]=S.[SeriesID] GROUP BY T.[TitleID], T.[SeriesID], T.[TitleOrder], T.[TitleName], S.[SeriesName] ORDER BY T.[SeriesID], T.[TitleOrder];

3
Test and save the query

Select Run and check that DisplaySeries contains a name only on the first title in each series. Save the query with a recognizable name.

4
Bind the form to the query

Open the form in Design View, set its Record Source property to the saved query, and set the series text box's Control Source to DisplaySeries. Keep any ID fields needed by filters, buttons, or other controls in the query.

5
Check the form's actions

Open the form in Form View and test its filters, combo boxes, and buttons. Confirm that the displayed rows remain ordered by series and title order.

If title order values repeat: The example requires a unique order value within each series. If values can repeat, add a unique tie-breaker to the comparison and sorting logic so exactly one record is treated as first.
Free Microsoft Office alternative

Work with Office documents in WPS Office

WPS Office is a free, lightweight alternative for everyday documents, spreadsheets, and presentations. Its familiar interface and Office format support make it easy to move existing files, but it does not replace Microsoft Access for editing Access databases or forms.

Review and organize exported Access data in spreadsheetsOpen and edit Microsoft Office formats such as DOCX, XLSX, and PPTXUse familiar document, spreadsheet, and presentation toolsMove existing Office files into a lightweight workspace
microsoft office alternative - wps office

Frequently Asked Questions

Why does the query need two copies of the title table?

The two aliases compare each title with titles in the same series that have an equal or earlier order. The first title is the only one with a count of one.

Can I edit records in a form based on this query?

The grouped query is generally read-only. If the form must edit records, use an editable record source and consider a different form design for displaying group headings.

Why did a series name disappear after I filtered the form?

A form filter can hide the record marked as first by the query. Check the form's Filter property and whether the first title in that series is included in the filtered results.

What if two titles have the same order value?

Add a unique tie-breaker, such as TitleID, to both the comparison logic and the ORDER BY clause. Otherwise, the query may not identify exactly one first title.