How to compare recordsets in access vba

[atksamy]

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

[NeoPa]

The first rs1 has random records. The second rs2 has the standard values for those records.

What does that mean? I have no idea what you're trying to say here.

[ajalwaysus]

If I understand this correctly, you are opening 2 record sets and you just start comparing the first record in rst1 to the first record in rst2.
This is not a good idea because you are assuming that the records exist in the exact same order in both tables/record sets. Just because it may be "A" before "B" before "C" in rst1 doesn't mean it will be the same in rst2, it could be "C" before "A" before "B" in rst2.

Is this what you are doing?

Also, it may help if you included how you defined your rst1 and rst2.

-AJ

[FishVal]

The part of your code which iterates recordsets to find matches could be well replaced with sql inner join.
Why pay more?

Kind regards,
Fish.

[ajalwaysus]

I am with Fish on this.

-AJ

[ADezii]

1. Do both Recordsets have exactly the same number of Records?
2. Do both Recordsets have exactly the same 4 Fields, and what are their Names?
3. Does each Recordset have a Unique Field, and if so what is it?
4. Do you wish to note each mismatch on Record Number/Field or Primary Key/Field?
   e.g. Record #4 mismatch on Fields 1 and 4
   Record #24 mismatch on Fields 1
   Record #112 mismatch on Fields 1, 2, and 4, etc.
   Primary Key 33456 mismatch on Fields, 3, and 4
   '...
5. You have more work to do before anyone of us can give you an answer.

[atksamy]

I am sorry for not being clear in the earlier instance. The first record set has a normal values like this which can be wrong
rs1

field1 field2 field3 field4
23 45 67 879
56 78 89 908
56 78 90 987

SEcond recordset has standard values
rs2
field1 field2 field3 field4
56 78 67 879
23 45 89 879
56 87 90 789


i want to compare each record of r1 to see if they match with any of the standard set of alues given in rs2. i,e all the four fields should match to be correct.
on the other hand i also want to know instances where field 3 and filed 4 only match and do not match.

There is no primary key or unique id here.
Both the record sets may or may not have same number of fields.

yes as per Adezii 's question I wish to note as many mismatches as possiblelike
record#2 mismtach on filed no 4

But the catch is each record of rs1 must be compared against all the record sof rs2 before these mismatches can be found.

Hope I am clear

[NeoPa]

You talk about normal values and standard values, implying they are different in some way, but I must admit I have no idea what you mean by these terms.

What is the definition of the terms you're using in this context?

[atksamy]

Standard values are reference values which are correct and present in second recordset.
Normal values are the values in first recordset which can have errors because they were input manually and may not match the standard reference values.

So i need to find out if the normal values match the standard values and if they don't then by how many fields per record is the mismatch happening.

[FishVal]

...
So i need to find out if the normal values match the standard values and if they don't then by how many fields per record is the mismatch happening.

The following query will return SV/NV records combinations (just as your nested loops) with count of fields matching:

SELECT tblNV.*, tblSV.*,
 -((tblNV.fld1=tblSV.fld1) + (tblNV.fld2=tblSV.fld2) + (tblNV.fld3=tblSV.fld3)  + (tblNV.fld4=tblSV.fld4)) AS MatchCount 
FROM tblNV, tblSV;

It could be filtered by [MatchCount] to return only appropriate matches, or/and aggregated to return records having no match.

[atksamy]

I tried using the query as specified by Fishval but the match count column is containing only #ERROR

[FishVal]

I have no doubts it works.

Kind regards,
Fish.

[atksamy]

Could the number of records be the reason as each recordset has around 18000 records

[FishVal]

Moon phase is a more probable reason.
Check your syntax and whether fields' data types match.
And ... I hope you try to run it in query designer, not as RecordSource of form/report/control.

[NeoPa]

Standard values are reference values which are correct and present in second recordset.
Normal values are the values in first recordset which can have errors because they were input manually and may not match the standard reference values.

Fish seems to understand you at least. I don't see how defining terms using other undefined terms (reference values) helps at all. I'm certainly still unclear as to what you're trying to explain.

However, I do understand that you seem to want to process through two recordsets of some 18,000 records apiece in a Cartesian Product fashion. This will cause a great deal of strain on the processor. It will take very much time.

Getting some of this to work intelligently in SQL (as I believe Fish is trying to lead you towards) seems to be the only hope of getting a manageable process.