Show a Series Name Only Once in a Microsoft Access Form
Question details
Show a series name only on the first record in each group in an 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.
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.
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.
In Microsoft Access, select Create > Query Design, then switch to SQL View.
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];
Select Run and check that DisplaySeries contains a name only on the first title in each series. Save the query with a recognizable name.
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.
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.
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.

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.




