Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Joining two tables where records are similar but not equal?

    I'm trying to join two tables where the records are similar, but not equal. For example:

    Table 1, Employee Name
    Doe, Johnathan A.

    Table 2, Employee Name
    Doe, John

    Is there a way to get the tables to have a relationship so I can pull data from both? One way I know of is to create a list in the master employee table (table 2) with the full name that would show in (table 1) and reference it that way, just wondering if there's a way that would avoid all the typing and constant updating.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    think abut it
    can your clearly define the rules as to how the SQL engine should perform a match
    what level of similarity equates to a match
    how many matches you expect for one record

    there are ways to find similarities in things like names (SOundex springs to mind)
    Issyrider advocates a related double metaphone???? as being superior to Soundex as its better at handling non English names

    the easiest way to get round this problem is not to let it happen in the first place. what you have is duplication of data and you need to stamp out that duplication not allow it to propagate inside the db. allowing it to remain encourages your users to be sloppy, it causes problems for you and other developers down the road.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2010
    Posts
    601
    I have used Soundex before for Fuzzy Logic matching. I had not heard of Double Metaphone

    I was curious. Found this:

    Soundex and Double Metaphone: Explanation of New Search Methods at WorldVitalRecords.com
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To expand on Healdem's suggestion, an employee table would be ideal in this case. You could add an Autonumber field to an Employee table and then store the ID of the employee in Table1 and Table2. Then when someone changes his/her name (just got married) then you don't have to change the name in multiple places.

Posting Permissions

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