Create Order Number Field in Query

[Jeremy]

I need to create an incremental number field in a query
that starts over on a change in Field A.

FieldA OrderNumber
A 1
A 2
A 3
B 1
B 2

Is there a way to do this either in code or in the query
itself.

Thanks
Jeremy

 

[John Verhagen]

Assuming the table is called tblIncrement, try the following code:
Function Increment()
Dim I As Integer, db As Database, rst As Recordset, LastField As String
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("Select * from tblIncrement ORDER BY FieldA")
With rst
Let I = 1
Let LastField = ""
Do While Not .EOF
' GET RANDOM NUMBER BETWEEN 1 AND !10
If .Fields("FieldA") <> LastField Then
Let I = 1
Let LastField = .Fields("FieldA")
End If
.Edit
.Fields("OrderNumber") = I
.Update
Let I = I + 1
.MoveNext
Loop
End With
End Function
There is no guarantee that when you view the records, the OrderNumber field
will be in any particular order.