Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: VBA code to reconcile data in two tables in MS Access

    Hello,

    I have two tables of names that I am trying to reconcile and find which names are missing from one table in the other. Many of these names are spelled and formatted differently. For example, a letter is missing in one table's version of the name, or one table will have a middle name and the other will not have one, or middle/last names are reveresed between tables, etc. Also some people have the same first or last names.

    The only way I can think of to reconcile these data items is to write a script that will loop through one table and attempt to match a string of the first 4 letters of each name from the other table to any part of a name in the table you are searching. If a match is found Access would display a box indicating the match between names and allow the user to confirm or deny the match. If it does not match the loop will continue to search for a match. Any names that don't find any confirmed matchs will be returned in a query or report.

    I have limited experience using VBA that does not include writing code that refers to two different tables. Any help with this (including an easier way to reconcile if you can think of one) would be really appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Imnot sure this can be done automatically, it may be best to plan on running a process (either a query or VBA) to find rows which are possible matches, which you can then correct or manipulate manually

    one thing you could consider using is an algorhythm such as soundex
    phonetic name search - Google Search

    bear in mind soundex in particular was designed for predominantly anglo saxon names and may struggle with non name or non english values
    Izyrider (I think) prefers to use something called double metanym / metanom? which apparently gives better matches on non english names.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    "Imnot sure this can be done automatically, it may be best to plan on running a process (either a query or VBA) to find rows which are possible matches, which you can then correct or manipulate manually"

    -This is what I am trying to do, any help that you could give me on how to do this is what I am looking for and would really appreciate!

    Thanks!

Posting Permissions

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