Others

Group Consecutive Records by ID and Color in Microsoft Access

Rana GarciaRana Garcia Nov 22, 2026 868 views

Question details

Group records by ID and consecutive color runs, returning the earliest start date and latest end date for each run.

Group Consecutive Records by ID and Color in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
An Access table contains records whose colors can change and later repeat within the same ID.
Observed behavior
The desired result has a separate row for each consecutive color run, even when the same color appears again later.
Before you start

Identify a numeric sequence field that uniquely orders records within each ID, and confirm that ID and Color contain no null values. The example uses YourTable, ID, Color, Seq, Start Date, and End Date; replace these names with your actual table and field names.

Solution 1Recommended

Assign run numbers with a recordset, then group the records

Use this method when you have an ordering field and can add a run-number field to the Access table.

A query grouped only by ID and Color merges matching colors even when they occur in separate runs. Assigning a run number first keeps those runs distinct.

The example resets the run number for each ID and increments it whenever the color changes. It writes the result to the source table, so rerun the procedure after changing the records or their order.

1
Add a run-number field

Open YourTable in Design View, add a field named RunNo with the Number data type and Long Integer field size, and save the table. If RunNo already exists, confirm that it can store the calculated numbers.

2
Create the VBA procedure

Open the Visual Basic editor from Database Tools, choose Insert > Module, and paste the following code. Adjust the table and field names if needed: Public Sub AssignColorRuns() Dim db As DAO.Database Dim rs As DAO.Recordset Dim firstRow As Boolean Dim previousID As Variant Dim previousColor As Variant Dim runNumber As Long Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM [YourTable] ORDER BY [ID], [Seq]", dbOpenDynaset) firstRow = True Do While Not rs.EOF If firstRow Then runNumber = 1 firstRow = False ElseIf rs![ID] <> previousID Then runNumber = 1 ElseIf rs![Color] <> previousColor Then runNumber = runNumber + 1 End If rs.Edit rs![RunNo] = runNumber rs.Update previousID = rs![ID] previousColor = rs![Color] rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End Sub

3
Assign the run numbers

Place the cursor inside AssignColorRuns in the VBA editor and choose Run > Run Sub/UserForm. Open YourTable and check that RunNo increases when Color changes and resets for a new ID.

4
Create the summary query

In Access, choose Create > Query Design, switch to SQL View, and enter this query. Run it to see one row per consecutive color run: SELECT [ID], [Color], [RunNo], Min([Start Date]) AS [First Start Date], Max([End Date]) AS [Last End Date] FROM [YourTable] GROUP BY [ID], [Color], [RunNo] ORDER BY [ID], [RunNo];

Assign run numbers with a recordset, then group the records
Sequence and data requirements: Seq must uniquely establish the intended order within each ID. The procedure assumes non-null ID and Color values and an updateable table; it overwrites existing RunNo values.
Free Microsoft Office alternative

Review exported Access data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported spreadsheet data. Export Access results to Excel to review and share them in WPS Spreadsheets; WPS does not run the Access query or DAO procedure.

Review exported color-run summaries in a spreadsheetOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for a straightforward move from OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I group the query by ID and Color alone?

Access combines all rows with the same ID and Color, including colors that reappear after another color. Add a run number based on the record order, then group by ID, Color, and RunNo.

What if my table has no sequence field?

Access cannot reliably determine which records are consecutive without an ordering field. Add or identify a field that captures the intended order, with a unique value within each ID.

Can I use a date instead of a sequence number?

You can order by a date if it represents the intended sequence, but ties need a reliable additional ordering field. The sample procedure uses a numeric Seq field that is unique within each ID.

Do I need to run the VBA procedure again after editing records?

Yes. If you add, remove, reorder, or change the ID or Color of records, run AssignColorRuns again before running the summary query so RunNo reflects the current data.

Does the query combine only overlapping date ranges?

No. It groups consecutive records with the same ID and Color regardless of date gaps or overlaps, then returns Min(Start Date) and Max(End Date) for each run.