Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    23

    Unanswered: Comparing Two Tables

    I want to use VBA to compare two tables and write out the field where there is no match. "Table A" is the main table and "Table B" is the varying table. Meaning to compare "Table B - fields" against "Table A - fields" to produce no match report. The reason that I want to use vba is because it is the best option. Can someone point or guide me on how to accomplish this. I will appreciate any help. Thank you very much in advance.

    Here are the tables
    Table A (Base Table)
    FIELD
    PROVIDER_ID
    LICENSE_NO
    LAST_NAME
    FIRST_NAME
    ETHNICITY
    GENDER
    DOB
    SSN
    TIN
    LICENSE_STATUS
    LIC_EXPIRATION_DATE
    PROFESSIONAL_DEGREE
    DEA_NO
    STATUS



    Table B (Varying Table)
    FIELD
    PROVIDER_ID
    LICENSE_NO
    LAST_NAME
    FIRST_NAME
    ETHNICITY
    GENDER
    DOB
    SSN
    TIN
    LICENSE_STATUS
    LIC_EXPIRATION_DATE
    PROFESSIONAL_DEGREE
    DEA
    STATUS
    Last edited by osimini; 10-27-09 at 19:49. Reason: none

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    SQL is ideal for what you want to do (unless I am not understanding). I write queries daily that compare two tables and show me the records that are in one table and not in another. Typcially you use a field or fields that is uniquely identifies a record and that is called the Primary key. In your case the SSN field (I am assuming this is the Social Security number) would be ideal if it is always populated.

    Create a query and add both tables to the query. Then drag the SSN field from one table to the other table. That creates a join (represented by a line) between the two tables. That will show you the rows that are in both tables. Then right click on the line to change the Join Properties. Then change the join to include all of the records from table A.

    Finally add the SSN fields from both tables to the output and put IS NULL in the criteria for TableB.SSN.

Posting Permissions

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