Comparing Records in two (2) Tables.

[RalphJ]

Hey Folks
I am relatively sure this is not that difficult but I can't seem to get there.

I have two (2) tables and each table has identical fields. One table will have more records than the other and I need to count the numbr of fields that match up in each record.

My assumption to this point is to have the file with the most records look at the file with the less records and count the number of times each field matches in the other record by record.

a quick example of this would be:
Table Cans:
01 04 07 12 16 19 59

Table Jugs:
01 04 12 16 26 39 59

Answer 5 which would be written to a table itself.


Any help is appreciated.

Thanks
RalphJ

[ssanfu]

There probably is a way to use queries to solve your problem, but I couldn't think of how to do it. So I whipped up some VBA.

You didn't say if the field type was numeric or text.... in this example, the field type is numeric. I created two tables and entered the numbers you provided. But You didn't say where you were going to save the match count.
Hopefully you will be able to modify this code to suit your purpose:

Option Compare Database
Option Explicit

Public Sub compareTables()
   Dim D As DAO.Database
   Dim C As DAO.Recordset
   Dim J As DAO.Recordset
   Dim sSQL As String
   Dim MatchCount As Integer
   Dim x As Integer
   Dim y As Integer
   Dim Upper As Integer
   Dim Lower As Integer

   Set D = CurrentDb

   Lower = 0   ' index of first field in recordset
   Upper = 6   ' index of last field in recordset

   sSQL = "SELECT Cans.Can1, Cans.Can2, Cans.Can3, Cans.Can4, Cans.Can5, Cans.Can6, Cans.Can7"
   sSQL = sSQL & " FROM Cans;"
   Set C = D.OpenRecordset(sSQL)

   sSQL = "SELECT Jugs.Jug1, Jugs.Jug2, Jugs.Jug3, Jugs.Jug4, Jugs.Jug5, Jugs.Jug6, Jugs.Jug7"
   sSQL = sSQL & " FROM Jugs;"
   Set J = D.OpenRecordset(sSQL)

   If (C.BOF And C.EOF) Or (J.BOF And J.EOF) Then
      'one or the other tables has no records
      C.Close
      J.Close
      Set C = Nothing
      Set J = Nothing
      Set D = Nothing
      Exit Sub
   End If

   'populate recordsets
   C.MoveLast
   C.MoveFirst

   J.MoveFirst
   J.MoveLast

   'Loop thru the recordsets
   Do Until C.EOF   'outer loop
      MatchCount = 0
      Do Until J.EOF   'inner loop
         For x = Lower To Upper   'c
            If Not IsNull(C.Fields(x)) Then
               For y = Lower To Upper  'j
                  If Not IsNull(J.Fields(y)) Then

                     'compare field values
                     If C.Fields(x) = J.Fields(y) Then
                        MatchCount = MatchCount + 1
                     End If

                  End If
               Next y
            End If
         Next x
         '-------------------
         'this is where you would write the value of
         ' MatchCount to a table
         MsgBox "Number of matches for this row is " & MatchCount
         '-------------------
         J.MoveNext
      Loop
      C.MoveNext

   Loop

   'clean up and exit
   C.Close
   J.Close
   Set C = Nothing
   Set J = Nothing
   Set D = Nothing
   Exit Sub

End Sub

[RalphJ]

Thanks Steve. I believe you helped me with some code before. I do appreciate the assistance. I will take a look at this and advise you on the result.
RalphJ

[RalphJ]

Thanks Steve. I believe you helped me with some code before. I do appreciate the assistance. I will take a look at this and advise you on the result.
RalphJ

Steve, would it be possible to modify the code that was done before to do what I want to do. I have inserted the code. You should recognized it as it is yours. In this situation Jugs is the larger file and I would be writing the results from counts of Cans into the Jugs table. The Jugs table at this point has 2400 records and the Cans table has 300 records. I am not sure that is clear enough but maybe another way to say it. I need to load record one of table Cans, look at each record in table Jugs counting the time the fields match per record. Write that to a the Jugs table. Then load the second record in table cans and doing the say thing into all records in tables can has looked at all records in table Jugs, and all matches have been calculated and wrote to fields in table Jugs i.e If the accumulated count for value 1 is 120 it would be written to Countfor01, and etc... for other counts.

When all is done the Jugs table would have for each record how many times the Cans table fields matched. i.e CountFor01 270, CountFor02 250 etc...

Here is the could as was written before: As I had told you it was only a bit slower than the Array.
Thanks again for you help.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myMsg As Byte
Dim RCount As Integer
Dim rstCount As Double
Dim RN01 As Integer
Dim RN02 As Integer
Dim RN03 As Integer
Dim RN04 As Integer
Dim RN05 As Integer
Dim RN06 As Integer

Dim N1 As Integer
Dim N2 As Integer
Dim N3 As Integer
Dim N4 As Integer
Dim N5 As Integer
Dim N6 As Integer

RN01 = Forms!fmResult!N01
RN02 = Forms!fmResult!N02
RN03 = Forms!fmResult!N03
RN04 = Forms!fmResult!N04
RN05 = Forms!fmResult!N05
RN06 = Forms!fmResult!N06

Set db = CurrentDb()
Set rst = db.OpenRecordset("Customers")

rst.MoveLast
rst.MoveFirst
RCount = 0
rstCount = 0

Do Until rst.EOF

N1 = rst!N01
N2 = rst!N02
N3 = rst!N03 'one disk access for data
N4 = rst!N04
N5 = rst!N05
N6 = rst!N06


' tests use mem var, not disk access
If RN01 = N1 Then RCount = RCount + 1
If RN01 = N2 Then RCount = RCount + 1
If RN01 = N3 Then RCount = RCount + 1
If RN01 = N4 Then RCount = RCount + 1
If RN01 = N5 Then RCount = RCount + 1
If RN01 = N6 Then RCount = RCount + 1

If RN02 = N1 Then RCount = RCount + 1
If RN02 = N2 Then RCount = RCount + 1
If RN02 = N3 Then RCount = RCount + 1
If RN02 = N4 Then RCount = RCount + 1
If RN02 = N5 Then RCount = RCount + 1
If RN03 = N6 Then RCount = RCount + 1

If RN03 = N1 Then RCount = RCount + 1
If RN03 = N2 Then RCount = RCount + 1
If RN03 = N3 Then RCount = RCount + 1
If RN03 = N4 Then RCount = RCount + 1
If RN03 = N5 Then RCount = RCount + 1
If RN03 = N6 Then RCount = RCount + 1

If RN04 = N1 Then RCount = RCount + 1
If RN04 = N2 Then RCount = RCount + 1
If RN04 = N3 Then RCount = RCount + 1
If RN04 = N4 Then RCount = RCount + 1
If RN04 = N5 Then RCount = RCount + 1
If RN04 = N6 Then RCount = RCount + 1

If RN05 = N1 Then RCount = RCount + 1
If RN05 = N2 Then RCount = RCount + 1
If RN05 = N3 Then RCount = RCount + 1
If RN05 = N4 Then RCount = RCount + 1
If RN05 = N5 Then RCount = RCount + 1
If RN05 = N6 Then RCount = RCount + 1

If RN06 = N1 Then RCount = RCount + 1
If RN06 = N2 Then RCount = RCount + 1
If RN06 = N3 Then RCount = RCount + 1
If RN06 = N4 Then RCount = RCount + 1
If RN06 = N5 Then RCount = RCount + 1
If RN06 = N6 Then RCount = RCount + 1

rst.Edit
rst!Mcount = RCount
rst.Update

rst.MoveNext
RCount = 0
rstCount = rstCount + 1

Loop

myMsg = MsgBox("We are done", vbOKOnly)

[RalphJ]

[QUOTE=RalphJ;55739]Steve, would it be possible to modify the code that was done before to do what I want to do. I have inserted the code. You should recognized it as it is yours. In this situation Jugs is the larger file and I would be writing the results from counts of Cans into the Jugs table. The Jugs table at this point has 2400 records and the Cans table has 300 records. I am not sure that is clear enough but maybe another way to say it. I need to load record one of table Cans, look at each record in table Jugs counting the time the fields match per record. Write that to a the Jugs table. Then load the second record in table cans and doing the say thing into all records in tables can has looked at all records in table Jugs, and all matches have been calculated and wrote to fields in table Jugs i.e If the accumulated count for value 1 is 120 it would be written to Countfor01, and etc... for other counts.

When all is done the Jugs table would have for each record how many times the Cans table fields matched. i.e CountFor01 270, CountFor02 250 etc...

"I was able to remove the code I had placed incorrectly"


STEVE
I posted the wrong code: I am working with your copy of the array code. Sorry I did not pay more attention to what I was copying.

Thanks Again
Ralph

Option Compare Database
Option Explicit
Option Base 1


Public Sub checkFields()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim myMsg As Byte
Dim RCount As Integer
Dim rstCount As Double
Dim RN(6) As Integer 'array
Dim rs(6) As Integer 'array

Dim o As Integer 'counter
Dim i As Integer 'counter
'-------------------------

RN(1) = Forms!fmResult!N01
RN(2) = Forms!fmResult!N02
RN(3) = Forms!fmResult!N03
RN(4) = Forms!fmResult!N04
RN(5) = Forms!fmResult!N05
RN(6) = Forms!fmResult!N06

Set db = CurrentDb()
Set rst = db.OpenRecordset("Customers")
rst.MoveLast
rst.MoveFirst

RCount = 0
rstCount = 0

Do Until rst.EOF

rs(1) = rst!N01
rs(2) = rst!N02
rs(3) = rst!N03
rs(4) = rst!N04
rs(5) = rst!N05
rs(6) = rst!N06

For o = 1 To 6
For i = 1 To 6
If RN(o) = rs(i) Then
RCount = RCount + 1
End If
Next i
Next o

rst.Edit
rst!Mcount = RCount
rst.Update

rst.MoveNext
rstCount = rstCount + 1
RCount = 0

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

MsgBox "We are done", vbOKOnly

End Sub

[ssanfu]

I set up a couple of tables and modified the code. But I am not sure what you want to do with the counts..

<snip>I need to load record one of table Cans, look at each record in table Jugs counting the time the fields match per record. Write that to a the Jugs table. Then load the second record in table cans and doing the say thing into all records in tables can has looked at all records in table Jugs, and all matches have been calculated and wrote to fields in table Jugs i.e If the accumulated count for value 1 is 120 it would be written to Countfor01, and etc... for other counts.

When all is done the Jugs table would have for each record how many times the Cans table fields matched. i.e CountFor01 270, CountFor02 250 etc...

Lets say the small table (S) has one row and the large table (L) has 3 rows. After comparing all of the fields in S to all of the fields in the first row of L, what should happen?

When moving to the next row of L, should the count of matches be reset to zero? Or be cumulative?

What is CountFor01, CountFor02?? What is the table name??
Do you want the individual count if numbers (ie how many 1's, how many 2's,...)???

[RalphJ]

I set up a couple of tables and modified the code. But I am not sure what you want to do with the counts..

Lets say the small table (S) has one row and the large table (L) has 3 rows. After comparing all of the fields in S to all of the fields in the first row of L, what should happen?

When moving to the next row of L, should the count of matches be reset to zero? Or be cumulative?

What is CountFor01, CountFor02?? What is the table name??
Do you want the individual count if numbers (ie how many 1's, how many 2's,...)???

Steve
The S having one row is compared to the L table having three rows, one row at a time. The L.Countfor01 would be filled if there was only 1 match in the comparision of Row 1 S to Row 1 L. Row 1 S would stay there and be compared to Row 2 L. The number of matches if 2 would be put in the L.Countfor02 field. There would be six in total of the "L.Countfor" Fields all in the L Table. Then if S had Row 2, S Row 2 would be compared to the three rows of L. The counter is set to zero after each comparision so the correct number of matches is updated to the L table. Individual numbers are not important, just how many matches each record has to the other.

Hell this is complicated when I try to explain it. I hope you are better at understanding than I am at writing.

S.Table
01 02 05 06 09 12

L.Table
01 02 11 14 16 21
02 05 12 07 26 31
05 09 13 17 26 39

S.Table has the following fields:
ID Autonumbering
N01 N02 N03 N04 N05 N06
01 02 05 06 09 12
L.Table has the following fields:
ID Autonumbering
N01 N02 N03 N04 N05 N06 Countfor1s (for Clarity) Countfor2s Countfor3s Countfor4s Countfor5s Countfor6s
01 02 11 14 16 21 2
02 05 12 07 26 31 3
02 05 06 09 14 25 4

With the above Row 1 S. Matches Row 1 L 2 times so the record is updated and the value ofCountforr2s is 2 etc....
Now if S.Table had a row 2 and it matched Row 1 of L.table with 2 numbers then L.Table Countfor2s value would be 4.

I hope this clears up some of the questions.

I have uploaded a little excel file to try to help

Thanks
RalphJ

[RalphJ]

Steve I don't see the excel file as an attachment. I went to Advance and selected the attachment icon and that went to a page where I tried to upload the file. Let me know if you can not find it and I will try again. in the post above if you don't see it the last digits i.e. the 2, 3 and 4 go under the countfor2s, countfor3s and countfor4s respectively.

I will tyr to attach the file to this reply.

Never mind I see the file is invalid. I will put in something else and try to attach it later.
RalphJ

Here is the file in Word format 2003. The page orientation is landscape.

[ssanfu]

S.Table
01 02 05 06 09 12

L.Table
01 02 11 14 16 21
02 05 12 07 26 31
05 09 13 17 26 39

N01 N02 N03 N04 N05 N06 Countfor1s (for Clarity) Countfor2s

One other thing... you have 01 02 05 ..... are these defined at text type fields or numeric (in the table)??

ID.......Autonumber
N01....Text
N02....Text


or

ID.......Autonumber
N01....Number (Integer)
N02....Number (Integer)


I'm guessing "Countfor1s" is a number??

[RalphJ]

One other thing... you have 01 02 05 ..... are these defined at text type fields or numeric (in the table)??

ID.......Autonumber
N01....Text
N02....Text


or

ID.......Autonumber
N01....Number (Integer)
N02....Number (Integer)


I'm guessing "Countfor1s" is a number??

They are all number fields.
Thanks
Ralph

[ssanfu]

Wasn't sure......OK, try this:

Public Sub compareTables()
   Dim D As DAO.Database
   Dim S As DAO.Recordset
   Dim L As DAO.Recordset
   Dim sSQL As String
   Dim MatchCount As Integer
   Dim x As Integer
   Dim y As Integer
   Dim iMax As Integer
   Dim iMin As Integer
   Dim tmp As Integer
   Dim Inner As Long
   Dim Outer As Long

   Dim L_tmp As Integer
   Dim S_tmp As Integer

   Set D = CurrentDb

   iMin = 0   ' index of first field in recordsets
   iMax = 5   ' index of last field in recordsets

   sSQL = "SELECT Small.N01, Small.N02, Small.N03, Small.N04, Small.N05, Small.N06"
   sSQL = sSQL & " FROM Small;"
   Set S = D.OpenRecordset(sSQL)

   sSQL = "SELECT Large.N01, Large.N02, Large.N03, Large.N04, Large.N05, Large.N06,"
   sSQL = sSQL & " Large.Countfor1s, Large.Countfor2s, Large.Countfor3s, Large.Countfor4s, Large.Countfor5s, Large.Countfor6s"
   sSQL = sSQL & " FROM Large;"
   Set L = D.OpenRecordset(sSQL)

   If (S.BOF And S.EOF) Or (L.BOF And L.EOF) Then
      'one or the other tables has no records
      S.Close
      L.Close
      Set S = Nothing
      Set L = Nothing
      Set D = Nothing
      Exit Sub
   End If

   'populate recordsets
   S.MoveLast
   S.MoveFirst
   L.MoveLast

   Outer = 0
   Inner = 0
   'Loop thru the recordsets
   Do Until S.EOF   'outer loop  -  this is by record
      Outer = Outer + 1
      L.MoveFirst
      Do Until L.EOF   'inner loop  -  this is by record
         Inner = Inner + 1
         MatchCount = 0
         For x = iMin To iMax   'S    - this is by field
            For y = iMin To iMax  'L   -  this is by field
               'compare field values
               S_tmp = S.Fields(x)
               L_tmp = L.Fields(y)
               If S.Fields(x) = L.Fields(y) Then
                  MatchCount = MatchCount + 1
               End If
            Next y
         Next x
         '-------------------
         'this is where you would write the value of
         ' MatchCount to a table
         If MatchCount > 0 Then
            tmp = MatchCount
            L.Edit
            L.Fields("Countfor" & tmp & "s") = Nz(L.Fields("Countfor" & tmp & "s"), 0) + MatchCount
            L.Update
         End If
         '-------------------
         L.MoveNext

         If Inner Mod 5 = 0 Then
            ' put some code here to update a couple of controls to show how
            ' many rows have been processed in both the outter (small) table
            ' and the inner (large) table.

            ' the variables "Outer" and "Inner" hold the numbers of records processed
            '-******************
            ' Me.SomeControl = Outer
            ' Me.AnotherControl = Inner
            ' Me.Repaint
            '-******************
         End If
      Loop
      S.MoveNext

   Loop

   'clean up and exit
   S.Close
   L.Close
   Set S = Nothing
   Set L = Nothing
   Set D = Nothing

   MsgBox "Done  "

End Sub

(Looks like something to do with the Lottery. If you win, do I get 10% ????? )

[RalphJ]

Wasn't sure......OK, try this:

Public Sub compareTables()
   Dim D As DAO.Database
   Dim S As DAO.Recordset
   Dim L As DAO.Recordset
   Dim sSQL As String
   Dim MatchCount As Integer
   Dim x As Integer
   Dim y As Integer
   Dim iMax As Integer
   Dim iMin As Integer
   Dim tmp As Integer
   Dim Inner As Long
   Dim Outer As Long
 
   Dim L_tmp As Integer
   Dim S_tmp As Integer
 
   Set D = CurrentDb
 
   iMin = 0   ' index of first field in recordsets
   iMax = 5   ' index of last field in recordsets
 
   sSQL = "SELECT Small.N01, Small.N02, Small.N03, Small.N04, Small.N05, Small.N06"
   sSQL = sSQL & " FROM Small;"
   Set S = D.OpenRecordset(sSQL)
 
   sSQL = "SELECT Large.N01, Large.N02, Large.N03, Large.N04, Large.N05, Large.N06,"
   sSQL = sSQL & " Large.Countfor1s, Large.Countfor2s, Large.Countfor3s, Large.Countfor4s, Large.Countfor5s, Large.Countfor6s"
   sSQL = sSQL & " FROM Large;"
   Set L = D.OpenRecordset(sSQL)
 
   If (S.BOF And S.EOF) Or (L.BOF And L.EOF) Then
      'one or the other tables has no records
      S.Close
      L.Close
      Set S = Nothing
      Set L = Nothing
      Set D = Nothing
      Exit Sub
   End If
 
   'populate recordsets
   S.MoveLast
   S.MoveFirst
   L.MoveLast
 
   Outer = 0
   Inner = 0
   'Loop thru the recordsets
   Do Until S.EOF   'outer loop  -  this is by record
      Outer = Outer + 1
      L.MoveFirst
      Do Until L.EOF   'inner loop  -  this is by record
         Inner = Inner + 1
         MatchCount = 0
         For x = iMin To iMax   'S    - this is by field
            For y = iMin To iMax  'L   -  this is by field
               'compare field values
               S_tmp = S.Fields(x)
               L_tmp = L.Fields(y)
               If S.Fields(x) = L.Fields(y) Then
                  MatchCount = MatchCount + 1
               End If
            Next y
         Next x
         '-------------------
         'this is where you would write the value of
         ' MatchCount to a table
         If MatchCount > 0 Then
            tmp = MatchCount
            L.Edit
            L.Fields("Countfor" & tmp & "s") = Nz(L.Fields("Countfor" & tmp & "s"), 0) + MatchCount
            L.Update
         End If
         '-------------------
         L.MoveNext
 
         If Inner Mod 5 = 0 Then
            ' put some code here to update a couple of controls to show how
            ' many rows have been processed in both the outter (small) table
            ' and the inner (large) table.
 
            ' the variables "Outer" and "Inner" hold the numbers of records processed
            '-******************
            ' Me.SomeControl = Outer
            ' Me.AnotherControl = Inner
            ' Me.Repaint
            '-******************
         End If
      Loop
      S.MoveNext
 
   Loop
 
   'clean up and exit
   S.Close
   L.Close
   Set S = Nothing
   Set L = Nothing
   Set D = Nothing
 
   MsgBox "Done  "
 
End Sub

(Looks like something to do with the Lottery. If you win, do I get 10% ????? )

Hi There
Thanks a million. I will have to try this later today, I have a group of honey do's to perform this morning for the misses and a stint at the doctors office for me this afternoon.
It is a lottery program but I do not have it all. When it is done the owners say the will provide a working version to us. I will let you know if it is any good. For my part you deserve the ten and more.

Again thanks for all you help and I will let you know about the code.
Ralph

[RalphJ]

Hi There
Thanks a million. I will have to try this later today, I have a group of honey do's to perform this morning for the misses and a stint at the doctors office for me this afternoon.
It is a lottery program but I do not have it all. When it is done the owners say the will provide a working version to us. I will let you know if it is any good. For my part you deserve the ten and more.

Again thanks for all you help and I will let you know about the code.
Ralph

Steve,
I have had an opportunity to run the code. I altered the array to fit the program and had 0-12 array. The code worked fine but is pretty slow. The compare was done between 600 records in the S.table and 3500 in the L.Table. It takes about 17-18 minutes to complete the comparision. It does provide the right answers when it is done, and updates each record accordingly.

I have also modified the code I had posted and tried to see if it would be quicker. It will compare the same amout of records in 9 minutes but it writes a cumulative total to the S.Table and writes the value to each record (does not update a single record, but all of them (600). I could not make it loop correctly so I used a GoTo statement. (I know it is not good code but I was just testing). I can't seem to stop the way the records are updated with that version of code. This is the Array version you had provide me some time ago.

Anyway, that is the story. If you have any ideas on how to speed it up I am open to suggestions. I do believe the older code that had the array would be faster if I could stop it from updating 600 records every time the values change.

Thanks
Ralph

[ssanfu]

I'll take a look at it this weekend.

things to do:

[RalphJ]

I'll take a look at it this weekend.

things to do:

Steve
Thanks a lot but I have overahauled the original code you sent me and used a goto stament for the outer loop. I have tested it this weekend and it works fine. The comparision of 3600 records to 600 records now takes more or less 2 minutes.

Again let me say thank you for all you help in both cases when I had troubles.
Thanks
RalphJ