locked
How to match records (Names or Phone Numbers) using multiple tables? RRS feed

  • Question

  • Hello everyone!

    I can find duplicate records in a single table but I do not have a clue as to how I can create a query that matches records in one table with records in another. Example: How do I check if First Names in table one is the same as First Names in table 2?

    If they match How do I delete the name from table one?

    I do not want to delete the records from both tables, just from table one.

    Thank for your help!

    Overdrive

    Tuesday, March 6, 2012 5:03 PM

Answers

  • I do list comparisons from Excel on a regular basis.  The first thing I would do is to make sure that you do not have duplicate fields for the fields you need to compare.  Depending on how large your lists are -- this could be tedious.  Example:

    Fname    Lname    Company    Phone
    Joe         Smith      ABC           123-4567
    Joseph    Smith      ABC           123-4567
    J             Smith      ABC           321-7456
    ...

    Once your lists are deduped you can do basic queries like

    find records in tbl1 that are not in tbl2

    select t1.* from tbl1 t1 left Join tbl2 t2 on t1.Fname = t2.Fname and t1.Lname = t2.Lname
    Where t2.Fname And t2.Lname Is Null

    and then find the records in tbl2 that are not in tbl1

    select t1.* from tbl2 t1 left Join tbl1 t2 on t1.Fname = t2.Fname and t1.Lname = t2.Lname
    Where t2.Fname And t2.Lname Is Null


    Rich P

    • Proposed as answer by -suzyQ Tuesday, March 6, 2012 7:42 PM
    • Marked as answer by Over Drive Tuesday, March 6, 2012 8:26 PM
    Tuesday, March 6, 2012 7:33 PM

All replies

  • Before your question could be answered correctly -- could you specify if these tables are related (or supposed to be related like with a primary and foreign key)?  Or are these just two independent data tables that you want to compare?

    Rich P

    Tuesday, March 6, 2012 6:10 PM
  • Before your question could be answered correctly -- could you specify if these tables are related (or supposed to be related like with a primary and foreign key)?  Or are these just two independent data tables that you want to compare?

    Rich P

    Tables are independent of each other. The data were imported from Excel, so I allowed Access to assigned a Primary key for each row. In this case, both tables will have identical keys. However I disabled the Primary Key from each table but I am still using their values to Join the tables.

    I tried the following Query:

    SELECT LOCAL.FIRST_NAME, REMOTE.FIRST_NAME, LOCAL.LAST_NAME, REMOTE.LAST_NAME
    
    FROM [LOCAL] RIGHT JOIN REMOTE ON LOCAL.ID = REMOTE.ID
    
    WHERE LOCAL.FIRST_NAME LIKE REMOTE.LAST_NAME OR LOCAL.LAST_NAME LIKE REMOTE.LAST_NAME;

    Thanks for your time!


    Tuesday, March 6, 2012 6:21 PM
  • you probably mean where local.First_name Like Remote.First_name ...

    WHERE LOCAL.FIRST_NAME LIKE REMOTE.LAST_NAME OR LOCAL.LAST_NAME LIKE REMOTE.LAST_NAME;


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Tuesday, March 6, 2012 6:36 PM
  • you probably mean where local.First_name Like Remote.First_name ...

    WHERE LOCAL.FIRST_NAME LIKE REMOTE.LAST_NAME OR LOCAL.LAST_NAME LIKE REMOTE.LAST_NAME;


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    But it's this the right way to go?

    I know SQL is not case sensitive, will it match the same names with dissferent letter casing?

    Tuesday, March 6, 2012 7:00 PM
  • you're good

    although I don't think using like that way is going to get you anywhere it will match as if you used =


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.


    • Edited by -suzyQ Tuesday, March 6, 2012 7:36 PM
    Tuesday, March 6, 2012 7:29 PM
  • I do list comparisons from Excel on a regular basis.  The first thing I would do is to make sure that you do not have duplicate fields for the fields you need to compare.  Depending on how large your lists are -- this could be tedious.  Example:

    Fname    Lname    Company    Phone
    Joe         Smith      ABC           123-4567
    Joseph    Smith      ABC           123-4567
    J             Smith      ABC           321-7456
    ...

    Once your lists are deduped you can do basic queries like

    find records in tbl1 that are not in tbl2

    select t1.* from tbl1 t1 left Join tbl2 t2 on t1.Fname = t2.Fname and t1.Lname = t2.Lname
    Where t2.Fname And t2.Lname Is Null

    and then find the records in tbl2 that are not in tbl1

    select t1.* from tbl2 t1 left Join tbl1 t2 on t1.Fname = t2.Fname and t1.Lname = t2.Lname
    Where t2.Fname And t2.Lname Is Null


    Rich P

    • Proposed as answer by -suzyQ Tuesday, March 6, 2012 7:42 PM
    • Marked as answer by Over Drive Tuesday, March 6, 2012 8:26 PM
    Tuesday, March 6, 2012 7:33 PM
  • You can also create a matched records query using the query wizard. That can be changed into a delete query and that will delete the duplicates.

    It can also be done using the QBE. Add the two tables. Create a relation for the field that you want to match. Add the field or "*" wildcard for the table you want to delete from and select the delete query option from the ribbon.

    HTH

    Imran

    Tuesday, March 6, 2012 7:36 PM