Looping in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
I am going to give this a go and will respond back with results. Thanks for
such a quick answer.
 
Back
Top