Is there a way to link Dissimilar fields/strings?

[Guest]

I am using Access to create a mailmerge. I receive a spreadsheet with
certain information on it. I have a table with Law Firm Names and address in
the database. The incoming spreadsheet is linked to the database and the Law
Firm Name is the joining field between the two tables. My problem is that
the incoming spreadsheet is always inconsistent in it's data entry of the law
firm names. So I always have to re-enter the names on the spreadsheet in
order for the link in the database to work. There is no other feature on the
incoming spreadsheet to link with the Law Firm names and addresses.

For example: In the Firm Address table the official name of the firm reads
like this: "James D. Angel L.L.P."
In the linked client data table the same firm is referenced as "James Angel".

Is there any way to work around all the extra data entry?

 

[Guest]

Although Lookup fields defined at the table level art thy creation of the
evil one, you might have a valid case for implementing a lookup in a new
column, to assist with paring up the records. You'll need to import the
spreadsheet as a new table, since you cannot make design changes to a linked
Excel spreadsheet. Once you get this data in a new table, go into table
design view. Add a new text field with a field size that is large enough to
accomodate your matching law firm names in the other table. Then, use the
dropdown arrow for data type, and select the last entry, which should read
"Lookup Wizard". Follow the prompts.


Reference: See the 2nd commandment here:
http://www.mvps.org/access/tencommandments.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[aaron.kempf]

yeah don't let people enter data into Excel.

your job is to put those Excel people on the street.

-Aaron

 

[John Vinson]

I am using Access to create a mailmerge. I receive a spreadsheet with
certain information on it. I have a table with Law Firm Names and address in
the database. The incoming spreadsheet is linked to the database and the Law
Firm Name is the joining field between the two tables. My problem is that
the incoming spreadsheet is always inconsistent in it's data entry of the law
firm names. So I always have to re-enter the names on the spreadsheet in
order for the link in the database to work. There is no other feature on the
incoming spreadsheet to link with the Law Firm names and addresses.

For example: In the Firm Address table the official name of the firm reads
like this: "James D. Angel L.L.P."
In the linked client data table the same firm is referenced as "James Angel".

Is there any way to work around all the extra data entry?

If you have inconsistent data... you have inconsistent data. It's hard
enough for a human being (even one who's familiar with the firms
involved) to know if these are the same - it's all but impossible for
a computer, which has no knowledge and no intelligence, to do so. Is
"JDA" the same as "James D. Angel L.L.P." or is it "Jones, Durant and
Alvarez"?

Controlled vocabulary exists for a good reason. Garbage in - garbage
out!

John W. Vinson[MVP]