Convert complex process that uses VBA recordsets

[sschrupp]

Oophda, this is a tough one to explain. The little gerbils in my brain are squeaking their wheels in a way that makes me think I can do this but I think they need a few nudges.

We have a table with account numbers, status codes, status dates, and a number of flags. Part of our metrics involves tracking how many accounts of one status code/status date combo there are, how many accounts of a 2nd status code/status date combo there are, and then changing flags on a number of accounts from both status code/status date combos equal to the lesser count of the two combos.

So currently I have a query that gets the counts of these combos using a couple sub-queries. I then use that query as a recordset in VBA and iterate through changing records based on the smallest combo count.

For example:
Account - Status Code - Status Date - Flag1 - Flag2
123 - 2W - 2/1/2010 - X - Null
124 - 2W - 5/7/2008 - X - Null
167 - 9W - 2/1/2010 - X - Null
178 - 9W - 2/1/2010 - Null - Null
193 - 9W - 2/1/2010 - X - Null
255 - 9W - 2/1/2010 - X - Null

What we want to do is match up the 2W's that share a Status Date and Flag1 marked X. Do the same with the 9W's. Then compare those two lists against each other.

In the above data sample we would have a count of 2 of the 2W's and a count of 3 of the 9W's.

Now we take the lesser count of 2 (from the 2 2W's) and for each count we want to change one of the 2W AND 9W records switching the Flag1 to Null and Flag2 to X. So we would end up switching 2 of the 2W's and 2 of the 9W's.

Now what's tickling the back of my brain is that it kind of seems like we could do some sort of non-matching type query or something rather than going record to record in some recordset. It's just escaping me how we could do that though.

Oh and one more thing. We don't care about key fields or anything at this point in the process. At this point we're strictly dealing with counts of records, not their details for the most part.

Thanks in advance!

Below is what we currently do (real code has a couple extra fields):

Dim acctSched As String
    Dim custNbr As String
    Dim statDate As Date
    Dim ssql As String
    Dim rsStat2and9Query As DAO.Recordset
    Dim rsCurrentTable As DAO.Recordset
    Dim currentSearch As String
    Dim iterationCount As Integer
    Dim stat2Count As Integer
    Dim stat9Count As Integer


    On Error GoTo ErrorHandler
    DoCmd.SetWarnings False
    
    ssql = "SELECT qryStat2W.CountOfAcctSchd AS [Stat 2 Count], " _
        & "qryStat9W.CountOfAcctSchd AS [Stat 9 Count], qryStat2W.[Cust Nbr], " _
        & "qryStat2W.[Status Date] " _
        & "FROM (SELECT Count(Master_Current_Details.AcctSchd) AS CountOfAcctSchd, " _
        & "Master_Current_Details.[Cust Nbr], Master_Current_Details.[Status Date] " _
        & "FROM Master_Current_Details " _
        & "WHERE (((Master_Current_Details.[Status Code])='2') AND " _
        & "((Master_Current_Details.[Term Code])='W') AND " _
        & "((Master_Current_Details.New)='X') AND " _
        & "((Master_Current_Details.BlankRunoffNew) Is Null)) " _
        & "GROUP BY Master_Current_Details.[Cust Nbr], " _
        & "Master_Current_Details.[Status Date]) AS " _
        & "qryStat2W INNER JOIN (SELECT Count(Master_Current_Details.AcctSchd) AS " _
        & "CountOfAcctSchd, Master_Current_Details.[Cust Nbr], " _
        & "Master_Current_Details.[Status Date] " _
        & "FROM Master_Current_Details " _
        & "WHERE (((Master_Current_Details.[Status Code])='9') AND " _
        & "((Master_Current_Details.[Term Code])='W') AND " _
        & "((Master_Current_Details.Runoff)='X') AND " _
        & "((Master_Current_Details.BlankRunoffNew) Is Null)) " _
        & "GROUP BY Master_Current_Details.[Cust Nbr], " _
        & "Master_Current_Details.[Status Date]) AS qryStat9W ON " _
        & "(qryStat2W.[Status Date] = qryStat9W.[Status Date]) AND " _
        & "(qryStat2W.[Cust Nbr] = qryStat9W.[Cust Nbr]);"

    Set db = CurrentDb()
    Set rsStat2and9Query = db.OpenRecordset(ssql)
    Set rsCurrentTable = db.OpenRecordset("Master_Current_Details")
    
    With rsStat2and9Query
        
        If .RecordCount < 1 Then
            Exit Function
        End If
        
        .MoveLast
        
        Do Until .EOF
            stat2Count = .Fields("[Stat 2 Count]").Value
            stat9Count = .Fields("[Stat 9 Count]").Value
            custNbr = .Fields("[Cust Nbr]").Value
            statDate = .Fields("[Status Date]").Value
            
            If stat2Count < stat9Count Then
                iterationCount = stat2Count
            Else
                iterationCount = stat9Count
            End If
                            
            Do Until iterationCount = 0
                With rsCurrentTable
                    currentSearch = "[Cust Nbr] = '" & custNbr & "' AND " _
                        & "[Status Date] = #" & statDate & "# AND [Status Code] = " _
                        & "'2' AND [Term Code] = 'W' AND [BlankRunoffNew] Is Null"
                    
                    .FindFirst (currentSearch)
                    
                    If .NoMatch = False Then
                        .Edit
                        .Fields("[New]").Value = Null
                        .Fields("[Runoff]").Value = Null
                        .Fields("[BlankRunoffNew]").Value = "X"
                        .Update
                    End If
                    
                    currentSearch = "[Cust Nbr] = '" & custNbr & "' AND " _
                        & "[Status Date] = #" & statDate & "# AND [Status Code] = " _
                        & "'9' AND [Term Code] = 'W' AND [BlankRunoffNew] Is Null"
                    
                    .FindFirst currentSearch
                    
                    If .NoMatch = False Then
                        .Edit
                        .Fields("[New]").Value = Null
                        .Fields("[Runoff]").Value = Null
                        .Fields("[BlankRunoffNew]").Value = "X"
                        .Update
                    End If
                End With
                
                iterationCount = iterationCount - 1
            Loop
            
            .MoveNext
        Loop
    End With

    DoCmd.SetWarnings True

    Exit Function
    
ErrorHandler:
    MsgBox "Failed to correct current table's 2W and 9W records."
    DoCmd.SetWarnings True
    End

End Function