Results 1 to 5 of 5
  1. #1
    Shido151 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    16

    Matching Non exact Cells

    I've a company database with everyone's software license but it doesn't have their employee ID number. I have another database with everyone's ID Number and I need to merge the two, How ever the problem is the names aren't exactly the same in both data bases. Some have small changes like



    John, Sargent M :: John, Sargent M.
    Samantha, Williams Anne :: Samantha, Williams A.
    it's all the people with more than 2 names entered that his problem happens the most and I was wondering how to solve this in access?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There is no sure-fire solution without consistency in data that can be relied on. Are the name parts all entered in one field as shown? Are last names always correct? Are there multiple employees with same last name?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Shido151 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    16
    The last names are 99% of the time the same. There are some of the same last names in multiple people. But here's and example.

    Asari, Kar A. Acrobat Pro ID #
    Asari, Kar A. Microsoft Visio ID #
    Asari, Kar Arunlam Adobe PhotoShop ID #
    Ataaq, Jazim M Microsfort Project ID #
    Avita, Mike E MCS Femap ID #
    Avita, Mike E Fortran ID #
    Avita, Mike E. Microsoft Project ID #
    Avita, Norman Acrobat Pro ID #
    Booth, Bill EasyDraw ID #


    Employee Name Employee ID
    Asari, Kar Arunlam 16596
    Asawinid, Watchaya 13188
    Asgha, Sabaah 168
    Ataaq, Jazim M 2543
    Attaria, Alexander 76342
    Attrevie, Tamun 446
    Avita, Mike E. 1454
    Avita, Norman 8544
    So now I need to import the ID numbers from Table1 (the table farthest Left) into the column labled ID# to the Right. As you can see not everyone is listed in the Software Database, and some people are listed multiple times because they have multiple software license issued to them.

    VLOOKUP in Excel does the trick except for one little problem and thats the Periods on the end of the names cause N/A error, but I also would rather know how to solve this in Access than Excel.



  4. #4
    Shido151 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Apr 2012
    Posts
    16
    I have no clue why the tables jumped across the screen like that, my bad.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    99% isn't total consistency. Less than 100% will result in match errors. You can try a join on LastName, FirstInitial. Maybe that will be 100% unique.

    If the names are in one field as shown, will have to extract the partial name string from the field in both tables in queries and use that constructed field to join the queries in an update action.

    x represents the Employee Name field
    Left(x, InStr(x,",")+2)

    Any variation in spelling of same person's name will result in errors. Run a test on copies of the tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query for similar but not exact data
    By markdudley in forum Queries
    Replies: 4
    Last Post: 03-09-2012, 01:02 PM
  2. Replies: 8
    Last Post: 03-01-2012, 08:37 AM
  3. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  4. Replies: 1
    Last Post: 08-11-2011, 11:33 AM
  5. Exact Match Hyperlink
    By JeffG3209 in forum Access
    Replies: 5
    Last Post: 06-23-2011, 07:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums