Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2017
    Posts
    3

    Unanswered: Query not matching Like names

    Greetings,
    I have two tables which contain names formatted as Last,First MI. I need to match records between the two tables to find the same person, however the name in one table might have a middle initial (or name), while the name in the other table won't.
    I'm using an append query to pull matching records, doing this by the Criteria of: Like [tblname].[fieldname] &"*"
    However the issue that I am discovering when using the Like and wildcard, is that it will pull one of the matches from one table, while not pulling the match from the other table. For example, if I have the name Doe,John in table1 and Doe,John R in table2 then when I am matching table1 to table2, it will return the record containing Doe,John R. When I match table2 to table1, it will not return the record containing Doe,John. It looks to me like the wildcard is looking for an actual character to be there, but can't recognize that there might not be a character.

    How can I solve this?

    Thank you,

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    557
    Provided Answers: 29
    You,can't match LIKE.

  3. #3
    Join Date
    Feb 2017
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    You,can't match LIKE.
    Ok... what can I do to compare two tables which hold names that might not be exact matches, in order to pull both records?

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,064
    Provided Answers: 11
    Ok... what can I do to compare two tables which hold names that might not be exact matches, in order to pull both records?
    You can't. You can join tables based on "=", "<>", "<=" or ">=". You might be able to use queries to parse the names into individual elements (first name, surname, etc) and then join the queries, but it won't give you a 100% success rate.

    Names are messy. I've recently taken part in an exercise to standardised one Lotus Notes Directory from another with additional data from HR. It was Not Fun.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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