Ms Access Comparing two recordsets

Question

Hi,

I am trying to compare two recordsets. the first rs1 has random records. The second rs2 has the standard values for those records. Initially I am looking to take each of the records and see if they match with the standard set of values in the second recordset. There are four fields in each record set to be compared and all four must match.

I just need some help in the loop. I am trying to write the non matching records to an excel file. Here is what I have so far

While Not rs1.EOF                                       
    With rs1
        .MoveFirst
        With rs2
            .MoveFirst
            While Not rs2.EOF
                counter = counter + 1
                a = 0
                If rs1!Kk = rs2!Kk Then a = a + 1
                If rs1!CC = rs2!CC Then a = a + 1
                If rs1!HN = rs2!HN Then a = a + 1
                If rs3!TN = rs2!TN Then a = a + 1

                If a > 3 Then GoTo correct
                .MoveNext

                If rs2.EOF Then
                    If rs!Table_Name <> "table1" Then
                        i = i + 1
                        j = 1
                        counter = counter + 1
                        objSht.Cells(i, j).Value = "casenum" & rs1.Fields(1)
                        j = j + 1
                        stat_counter = stat_counter + 1
                    End If

                    If i = 65500 Then
                        Set wbexcel = objexcel.ActiveWorkbook
                        ''//Set objSht = wbexcel.Worksheets("Sheet2")
                        Set objSht = wbexcel.Worksheets.Add
                        i = 2
                    End If
                End If                                        
correct:
                rs1.MoveNext
            Wend
        End With
    End With

Also any ideas on how i can segregate based on 2 of fields matching with standard and 3 of the fields matching with the standard values

Answer 1

My gut says you are doing something sub-optimally; however, in the if statement If rs2.EOF Then, why not add a comparison to a and then redirect to a different Excel file for 0, 1, 2 and 3

Bruit Force and Ignorance, but definitely segregated.

Answer 2

Are the recordsets already sorted? I'm guessing that's the case since you move to the next rs2 on a non match. Personally i'd specify a sort to make 100% sure.

Also I'd test this pretty thoroughly with a small test dataset with a few edge cases to make sure you get what you expect.

With the above in mind your code looks like it'd work but i have a few small recommendations to make it easier to read.

First i'd recommend ditching the nested With rs1 and With rs2. Just refer to each recordset explicitly so you can clearly see what is happening to each rs. eg:

If a > 3 Then GoTo correct
                .MoveNext

becomes

If a > 3 Then GoTo correct
               rs2.MoveNext

Next your if statements with a = a + 1 could do with some tidying. eg:

If rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN and rs3!TN = rs2!TN then
  ''// Do Nothing or maybe increase a count or whatever :)
else
  WriteToExcel(objSht , rs1.fields)
end if

You'll need to write a function called WriteToExcel() but this will make the next step easier. I think you want to write to different sheets depending on the matches?

If rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN and rs3!TN = rs2!TN then
  ''// Do Nothing
else if rs1!Kk = rs2!Kk and rs1!CC = rs2!CC and rs1!HN = rs2!HN then
  WriteToExcel(objSht2 , rs1.fields)
else
  WriteToExcel(objSht , rs1.fields)
end if

You may also want to look at switches in the case where you need any two matches, rather than specific matches as above... oh and variable j seems a bit superfluous.