Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching mismatched fields (2000)

    Is there a way to 'match' two tables using the name field, where one table is FIRST MIDDLE LAST and the other is LAST, FIRST MIDDLE? I would suppose it requires a query/make table that can take one field's data and tell it to reposition the information, such as 'take everything up to the comma and move to the end, dropping in a space and deleting the left over comma. and space?
    Then somehow applying fuzzy logic to search for matches?

    Is this even theoretically possible? What do folks do when data order doesn't match?

    Thanks for all the great thoughts I know I'm going to get on this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching mismatched fields (2000)

    Creating a query that displays the name fields according to the same format isn't that hard (see below), but applying fuzzy logic is much harder - you'd have to think about what exactly <img src=/S/grin.gif border=0 alt=grin width=15 height=15> you mean by fuzzy.

    Say that the field with format LAST, FIRST MIDDLE is named LastFirst. The following expression will convert this to FIRST MIDDLE LAST format.

    FirstLast: Mid([LastFirst],InStr([LastFirst],",")+2) & " " & Left([LastFirst],InStr([LastFirst],",")-1)

    You can compare this directly to the FIRST MIDDLE LAST field in the other table. But again, if you want to do a fuzzy compare, you'd have to decide what differences you'd allow: one letter different in the same location (Jones vs Jomes), transposed letters (Wharton vs Hwarton), abbreviations (Charles vs Chas), omissions (Alessi vs Alesi), ...

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching mismatched fields (2000)

    Hans,
    Thanks so much for the query. We'll play with that and see what it spits out before we try anything more.
    With much appreciation,
    Judy

Posting Permissions

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