Group Consecutive Records by ID and Color in Microsoft Access
Question details
Group records by ID and consecutive color runs, returning the earliest start date and latest end date for each run.

- 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.
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.
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.
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.
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
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.
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];

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.

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.




