G
Guest
First of all I realize that this is an extra long post and I'm guessing a
challenging one, too. I would be willing to offer compensation to anyone who
is willing to help me accomplish this task. Please provide me with your
contact information and I will be in touch to discuss. A gracious thank you
in advance for taking the time to read, ponder and possibly suggest a
solution!
I’m using MS Access 2002 sitting on top of a MySql database (ODBC) and am
trying to create a link between corresponding records in two different
tables. We store our data in the MySql database and use Access to work with
the data. We currently have four tables with a total of about 3.5 million
records containing around 60 fields per record. Three tables are very
similar, each containing property information collected by the tax appraiser
for their respective counties. The ‘corporation’ table is different than the
tax appraiser tables and is much larger with about 3 million records. We
need to create a link between about 5,000 records we have identified (using
queries) in the three tax appraiser tables and their corresponding records in
the corporation table. I’m guessing that the best way to do this may be to
append the unique ‘corporate number’ from the corporate table to the
corresponding record in the tax appraiser table. The unique corporation
number would then become our common element between the two records with
which to create the link.
Two things are making this extra challenging for me: (1) the corporate name
in the tax table (the search criteria for searching the corporation table)
may not match-character-for-character the corporate name in the corporate
table due to abbreviations, misspellings, etc… and (2) using the tax
appraiser value for ‘corporation name’ as criteria to search the corporation
table may produce multiple matches since different corporations can have the
exact same name (but have unique corporate numbers). In these cases, clues
from the address fields often lead us to the correct match.
My best guess of a possible approach to automating the process of appending
the corporate number from the corporate table to the corresponding
corporation in the tax appraiser table:
1. Search for exact matches of corporate name and street address (or maybe
just street number and zip code since abbreviations are often used for
‘road’, ‘street’, etc…) using the tax appraiser table’s values to search the
corporate table. For these ‘exact’ matches the computer could automatically
append the corporate number to the corresponding record in the tax table
without user verification, except in cases where there are multiple matches.
For cases where there are multiple matches, the user could select the correct
record from a list of matches.
2. Search for matches of street number and zip from table to table to catch
some of the matches that were missed in step one. We could follow the same
process as in step one for multiple matches.
3. To automate matching records that steps (1) and (2) missed we could
search the first few characters of the corporation and matches for either
city, zip, street number, etc… We could follow the same process as in step
one for multiple matches.
5. And so on, until all corporations are linked. The ones that we can’t
match through this process will have to be done manually. For some of the
records, we won’t be able to identify the correct match. In these cases
we’ll just have to use the name and address supplied by the tax appraiser for
our mailings.
Thank you so very much for reading and for any possible help! I eagerly
anticipate your response!
Regards,
Joel Sivertsen
Commercial-Investment Real Estate
challenging one, too. I would be willing to offer compensation to anyone who
is willing to help me accomplish this task. Please provide me with your
contact information and I will be in touch to discuss. A gracious thank you
in advance for taking the time to read, ponder and possibly suggest a
solution!
I’m using MS Access 2002 sitting on top of a MySql database (ODBC) and am
trying to create a link between corresponding records in two different
tables. We store our data in the MySql database and use Access to work with
the data. We currently have four tables with a total of about 3.5 million
records containing around 60 fields per record. Three tables are very
similar, each containing property information collected by the tax appraiser
for their respective counties. The ‘corporation’ table is different than the
tax appraiser tables and is much larger with about 3 million records. We
need to create a link between about 5,000 records we have identified (using
queries) in the three tax appraiser tables and their corresponding records in
the corporation table. I’m guessing that the best way to do this may be to
append the unique ‘corporate number’ from the corporate table to the
corresponding record in the tax appraiser table. The unique corporation
number would then become our common element between the two records with
which to create the link.
Two things are making this extra challenging for me: (1) the corporate name
in the tax table (the search criteria for searching the corporation table)
may not match-character-for-character the corporate name in the corporate
table due to abbreviations, misspellings, etc… and (2) using the tax
appraiser value for ‘corporation name’ as criteria to search the corporation
table may produce multiple matches since different corporations can have the
exact same name (but have unique corporate numbers). In these cases, clues
from the address fields often lead us to the correct match.
My best guess of a possible approach to automating the process of appending
the corporate number from the corporate table to the corresponding
corporation in the tax appraiser table:
1. Search for exact matches of corporate name and street address (or maybe
just street number and zip code since abbreviations are often used for
‘road’, ‘street’, etc…) using the tax appraiser table’s values to search the
corporate table. For these ‘exact’ matches the computer could automatically
append the corporate number to the corresponding record in the tax table
without user verification, except in cases where there are multiple matches.
For cases where there are multiple matches, the user could select the correct
record from a list of matches.
2. Search for matches of street number and zip from table to table to catch
some of the matches that were missed in step one. We could follow the same
process as in step one for multiple matches.
3. To automate matching records that steps (1) and (2) missed we could
search the first few characters of the corporation and matches for either
city, zip, street number, etc… We could follow the same process as in step
one for multiple matches.
5. And so on, until all corporations are linked. The ones that we can’t
match through this process will have to be done manually. For some of the
records, we won’t be able to identify the correct match. In these cases
we’ll just have to use the name and address supplied by the tax appraiser for
our mailings.
Thank you so very much for reading and for any possible help! I eagerly
anticipate your response!
Regards,
Joel Sivertsen
Commercial-Investment Real Estate
