J
Joelzzzz
First of all I realize that this is an extra long post and I'm guessing a
challenging one, too. A gracious thank you in advance for taking the time to
read, ponder and possibly suggest a solution!
I’m using MS Access 2002 and am trying to create a link between
corresponding records in two different tables. We store our data in a 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!
challenging one, too. A gracious thank you in advance for taking the time to
read, ponder and possibly suggest a solution!
I’m using MS Access 2002 and am trying to create a link between
corresponding records in two different tables. We store our data in a 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!