For Each Change in Value

[rgrogan]

Hello...I'm new to this forum...I hope you can help me.

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 for field X. 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

 

[a.sharma]

rgrogan,
How are you checking the values of A and B?
Are these values in a table, query or a form. Give some more details and I can try to help you.

 

[rgrogan]

Someone from another forum gave me the code I was looking for (Below). Thanks for your help!

dim rs as dao.recordset
dim strSQL as string
dim holdA, holdB, HoldX
strSQL = "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

 

[spikepl]

Cross-posting on different fora without notification thereof will get you - quite deservedly - in the doghouse pretty quickly.

Read this post http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

 

[rgrogan]

Thanks for the heads up...I had no idea.

rg