Results 1 to 7 of 7

Thread: Match records between two tables

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    13

    Match records between two tables

    I have the below code to do matching between two different tables. The code only updates the first record as "Matched".

    I want to compare each record in ID field from T1 if it present in T2, e.g. To check if A present in T2 then go to next record in T1 and check B if it present in T2 through loop until all records in T1 matched

    Table 1

    ID
    A
    B
    C

    Table 2

    ID
    A
    B

    Expected Matching Results

    ID
    A
    B
    Any help please

    If rs2("ID").Value = rs1("ID").Value Then
    rs2.MoveNext()
    Do While Not rs2.EOF()

    rs1("Matching").Value = "Matched"
    rs1.Update()
    rs2.MoveFirst()
    Loop

    End If

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Match records between two tables

    This is much easier to do in SQL:
    Code:
    Update table1
    Set field = newValue
    from table1
    inner join table2 on table1.id = table2.id
    But to directly answer the question, it only updates the first record of Table1 because you never move to the second record. You only move through table2.

    You need to loop through both tables.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: Match records between two tables

    Plus you have an endless loop,

    Code:
    Do While Not rs2.EOF()
    
    rs1("Matching").Value = "Matched"
    rs1.Update()
    rs2.MoveFirst()
    Loop
    This "rs2.MoveFirst()" need to be moved outside of the loop.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    13

    Re: Match records between two tables

    Thanks techgnome
    Can you advise with correct code that solve the issue

  5. #5

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    13

    Re: Match records between two tables

    Thanks techgnome
    Can you advise with correct code that solve the issue

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    13

    Re: Match records between two tables

    Thanks wes4dbt
    Can you help with correct code?

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    13

    Re: Match records between two tables

    Thanks wes4dbt
    Can you help with correct code?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width