Create a query with a derived field based on each change in recordset

[rgrogan]

I need to come up with a way to derive field X (see below) in a query.

For each change in field A, Set X=1
For each change in field B, X=X+1

The below table shows what the query results should look like. Thanks for your help!

A	X	B
A14-040	1	971
A23-020	1	676
A35-032	1	300
A35-032	2	776
A35-032	2	776
A35-032	2	776
A35-032	2	776
A35-032	2	776
A35-032	2	776
A35-032	3	500
B45-031	1	776
B45-031	1	776
C27-010	1	776
C27-010	2	999
C27-010	2	999

[June7]

Calculations dependent on values in another record of same table are difficult. You essentially want to assign a value to record groups. Review: http://allenbrowne.com/ranking.html

[rgrogan]

Calculations dependent on values in another record of same table are difficult. You essentially want to assign a value to record groups. Review: http://allenbrowne.com/ranking.html

Thanks! I had someone on another forum respond with a solution (Below). Thanks for you help!

Dim rs As DAO.Recordset
Dim strSQL As String
Dim holdA, holdB, Hold
XstrSQL = "SELECT A, X, B FROM YourTable ORDER BY A, B"
Set rs = CurrentDB.OpenRecordset( strSQL, dbOpenDynaset)
holdA = ""
holdB = ""
holdX = 0
Do Until rs.EOF     
rs.Edit     
If rs!A <> holdA Then           
   holdX = 1     
ElseIf  rs!B <> holdB Then          
   holdX = holdX  + 1     
End If 
rs!X = holdX     
holdA = rs!A 
holdB = rs!B     
rs.Update     
rs.MoveNext
Loop