Looping in VBA

[Guest]

I have a series of data where I need to count the number of times an item
changes hands. In the subSet of data here I would count "1" for each time the
[EMP_ID] & [ROLE_DESC] changes. You can not group on [EMP_ID]&[ROLE_DESC]
because the same combo may be in diff days. But the [date] filed creates a
problem in that the same [EMP_ID]&[ROLE_Desc] may be in consecutive days. The
only way I see to do this is to write some VBA code to loop through the data
incrementing a counter everytime the combination of [EMP_ID]&[ROLE_DESC]
changes.

If someone agrees; would you also know how to write the Looping code? Loops
are total new to me.

***DATA EXAMPLE***
Date EMP_ID ROLE_DESC
01/03/2006 30796 Sales
01/03/2006 30796 UW 301-500
01/03/2006 44768 Issuer
01/03/2006 52974 Issuer
01/03/2006 52974 Life Underwriting QA
01/05/2006 52974 Life Underwriting QA
11/01/2005 48609 Submission Specialist
11/14/2005 24189 Submission Specialist
11/15/2005 24189 Submission Specialist
11/23/2005 24189 Submission Specialist
12/20/2005 24189 UW 301-500
12/21/2005 44768 Sales
12/21/2005 44768 UW 301-500
12/23/2005 25072 Sales
12/23/2005 30796 Sales
12/23/2005 30796 UW 301-500
12/23/2005 39676 Associate
12/23/2005 39676 UW 301-500
12/23/2005 44768 Associate
12/23/2005 44768 Sales
*******************
In this example there should 17 handoffs.
If you count records it's 20, if you group on [EMP_ID]&[ROLE_DESC] it's 14.

Any other ideas would be welcome as well.

Thanks

 

[Guest]

Public Sub CountChanges()
Dim intI As Integer
Dim intChanges As Integer
Dim rst As Recordset
Dim strLastEmp As String
Dim strLastRole As String

Set rst = CurrentDb.OpenRecordset("_sometest")
rst.MoveLast
rst.MoveFirst

strLastEmp = rst![emp_id]
strLastRole = rst![role_desc]
intChanges = 1

Do While Not rst.EOF
If rst![emp_id] <> strLastEmp Or rst![role_desc] <> strLastRole Then
intChanges = intChanges + 1
strLastEmp = rst![emp_id]
strLastRole = rst![role_desc]
End If
rst.MoveNext
Loop
Debug.Print intChanges
Set rst = Nothing
End Sub

 

[Guest]

I am going to give this a go and will respond back with results. Thanks for
such a quick answer.