Compare records in 2 tables

[RalphJ]

Good day to all
I am trying to solve the problem of reviewing records from 2 different tables. Both tables have the same fields both in Name and type. There is 6 fields to each table to compare. Each field can have a different number up to 42. I am trying to see how many of the numbers in a given record for table 1 matches the records in table 2. below is an example of what I am doing.

Tble1 Tble2
N1 = 2 N1 = 5
N2 = 24 N2 = 2
N3 = 5 N3 = 10
N4 = 9 N4 = 31


N5 = 10 N4 = 36
N6 = 29 N6 = 28

Using the if statments I am currently using this will deliver me 3 matching numbers on 2, 5 and 10.

As I said I am using IF staments to this right now. Is there a better, more efficent way to accomplish the same thing. Tble1 is a small table but Tble2 has a large number of records and process time is about 25 minutes.

Thanks
RalphJ

[ajetrumpet]

I would tend to think that IIFS(), even nested ones, would be faster than subqueries.

but the question remains...for each record in table A, do you want a count of EACH occurance found in table B, regardless of field and/or record location? for each number that's found?

or do you want to just assign 1's for each number in the record from table that is found SOMEWHERE in table B, regardless of field and/or record location?

differentiating between those two situations might get you a quicker method. for instance, IDK if this would be faster or not, but DCOUNT() is available, either through the query interface or code. You could use that on all the fields, sum the 1's from that, and reiterate that through every records.

Even better than that, would be storing the numbers previously found (speaking of coding here) in a var, or an array. That way, each subsequent loop via the coding function could utilize a small conditional statement instead of requesting the engine's attention to use a DCOUNT() function.

But with all that I've just said, I would tend to guess that the processing time wouldn't change much. Maybe a reduction in minutes, but I doubt it would be more than 5-10 minutes, if it's taking you 25 min now. But I'm no expert at guesswork like that, so take it for what you will!

[RalphJ]

I would tend to think that IIFS(), even nested ones, would be faster than subqueries.

but the question remains...for each record in table A, do you want a count of EACH occurance found in table B, regardless of field and/or record location? for each number that's found?

or do you want to just assign 1's for each number in the record from table that is found SOMEWHERE in table B, regardless of field and/or record location?

differentiating between those two situations might get you a quicker method. for instance, IDK if this would be faster or not, but DCOUNT() is available, either through the query interface or code. You could use that on all the fields, sum the 1's from that, and reiterate that through every records.

Even better than that, would be storing the numbers previously found (speaking of coding here) in a var, or an array. That way, each subsequent loop via the coding function could utilize a small conditional statement instead of requesting the engine's attention to use a DCOUNT() function.

But with all that I've just said, I would tend to guess that the processing time wouldn't change much. Maybe a reduction in minutes, but I doubt it would be more than 5-10 minutes, if it's taking you 25 min now. But I'm no expert at guesswork like that, so take it for what you will!

Thanks Adam

I am using VBA now to get to where I am. I was wondering if something like Arrays, for and next loops etc... woould help anything.
Regards
RalphJ

[ssanfu]

I am using VBA now to get to where I am. I was wondering if something like Arrays, for and next loops etc... woould help anything.
Regards
RalphJ

Maybe post your code... then we might be able to suggest time savers.

[RalphJ]

Maybe post your code... then we might be able to suggest time savers.

Here is the code. As you can see I declare some variables to pick up one side of the tables and then open a recordset to access the table that has the comparision records

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

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.MoveFirst
RCount = 0
rstCount = 0

Do Until rst.EOF
rst.Edit

If RN01 = rst!N01 Then RCount = RCount + 1
If RN01 = rst!N02 Then RCount = RCount + 1
If RN01 = rst!N03 Then RCount = RCount + 1
If RN01 = rst!N04 Then RCount = RCount + 1
If RN01 = rst!N05 Then RCount = RCount + 1
If RN01 = rst!N06 Then RCount = RCount + 1

If RN02 = rst!N01 Then RCount = RCount + 1
If RN02 = rst!N02 Then RCount = RCount + 1
If RN02 = rst!N03 Then RCount = RCount + 1
If RN02 = rst!N04 Then RCount = RCount + 1
If RN02 = rst!N05 Then RCount = RCount + 1
If RN02 = rst!N06 Then RCount = RCount + 1

If RN03 = rst!N01 Then RCount = RCount + 1
If RN03 = rst!N02 Then RCount = RCount + 1
If RN03 = rst!N03 Then RCount = RCount + 1
If RN03 = rst!N04 Then RCount = RCount + 1
If RN03 = rst!N05 Then RCount = RCount + 1
If RN03 = rst!N06 Then RCount = RCount + 1

If RN04 = rst!N01 Then RCount = RCount + 1
If RN04 = rst!N02 Then RCount = RCount + 1
If RN04 = rst!N03 Then RCount = RCount + 1
If RN04 = rst!N04 Then RCount = RCount + 1
If RN04 = rst!N05 Then RCount = RCount + 1
If RN04 = rst!N06 Then RCount = RCount + 1

If RN05 = rst!N01 Then RCount = RCount + 1
If RN05 = rst!N02 Then RCount = RCount + 1
If RN05 = rst!N03 Then RCount = RCount + 1
If RN05 = rst!N04 Then RCount = RCount + 1
If RN05 = rst!N05 Then RCount = RCount + 1
If RN05 = rst!N06 Then RCount = RCount + 1

If RN06 = rst!N01 Then RCount = RCount + 1
If RN06 = rst!N02 Then RCount = RCount + 1
If RN06 = rst!N03 Then RCount = RCount + 1
If RN06 = rst!N04 Then RCount = RCount + 1
If RN06 = rst!N05 Then RCount = RCount + 1
If RN06 = rst!N06 Then RCount = RCount + 1



rst!Mcount = RCount
rst.Update
rst.MoveNext
RCount = 0
rstCount = rstCount + 1

Loop

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

[ssanfu]

I don't know if this will run any faster, but try this:

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

(looks like this has something to do with a lottery )

[RalphJ]

I don't know if this will run any faster, but try this:

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

(looks like this has something to do with a lottery )

ssanfu
Thanks a lot the code cut the time from 22 minutes to 4 minutes.
In answer to your question it may be, I only have this part of the system. There are 12 other parts that are in other peoples hands.

Again thanks a lot for the assistance.
RalphJ

[ssanfu]

I think the recordset accesses were eating up the time. If you have time, try this modification of your code:

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]

I think the recordset accesses were eating up the time. If you have time, try this modification of your code:

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)

Thanks I will try the code tomorrow and see what happens. It is getting close to carnival time in Brasil and everything will stop for a few days.
Thanks again
RalphJ

[RalphJ]

I think the recordset accesses were eating up the time. If you have time, try this modification of your code:

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)

ssanfu,
I ran the code tonight and found it was about as fast as the array loop. There was about 30 seconds different in time.
Again thanks a lot for your assistance!

RalphJ

[ssanfu]

Glad it is working for you.

Are you ready to make this solved?

[RalphJ]

Glad it is working for you.

Are you ready to make this solved?

Yes, How do I go about doing that?

[ssanfu]

At the top of each thread, there is a link called 'Thread Tools'. By clicking on this link, a menu will appear with a number of options. The bottom one is "Mark this thread as solved"

I marked it....

[RalphJ]

At the top of each thread, there is a link called 'Thread Tools'. By clicking on this link, a menu will appear with a number of options. The bottom one is "Mark this thread as solved"

I marked it....

Thanks again