Relation based counter

[Megawisdumb]

Below is the final 3 column output I need in a query or table. The source is a query with the [ID] and [seg] fields only. The relation field is simply the count of the "CLP" seg until another "CLP" seg is encountered. My question is can this be done within a standard query or is recordset code needed?

ID seg Relation
1 CLP 1
2 NM1 1
3 NM1 1
4 DTM 1
5 DTM 1
6 CLP 2
7 DTM 2
8 DTM 2
9 REF 2
10 CLP 3
11 SVC 3
12 DTM 3
13 DTM 3

 

[Megawisdumb]

DCount?

I'm uncertain how the Dcount feature would assign a value to the records under the first CLP. I was thinking some type of loop would be needed (failed example attempt below). The key relation is that the 1st Seg field that has a CLP record gets a value that continues until the next CLP record. The relation field is where I intended to store the value. Can this be done with DCount?


Function AssignRelations()

Dim db As Database
Dim rst As DAO.Recordset
Dim Count As Integer
Dim Claim As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("query 1")
Count = 1
Claim = 1

Do
Do While Count < 20
Count = Count + 1
If rst("seg") = "CLP" Then
rst.Edit
rst!["Relation"] = Count
rst.Update
If rst("seg") <> "CLP" Then
rst.Edit
rst!["Relation"] = Count
rst.Update
Exit Do
End If
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing


End