Data comparison problem

[22strider]

Hello Friends
Could you please let me know the best approach to deal with the following issue?
I have two sets of data with fields “MANUFACTURER NAME” and “MANUFACTURER PN”. I have to compare them and find discrepancies between them.
The problem is that in these two data sets there are some manufacturers names that I know are the same but I can’t figure out the best way of telling my SQL queries to treat them as same. For example; for a particular MANUFACTURER PN data set #1 may have manufacturer name VISHAY and the data set #2 may have VISHAY SPRAGUE; I need to mark that record as same. This naming is not consistent in some other MANUFACTURER PN data set#1 may have VISHAY SPRAGUE and data set#2 may have VISHAY.
There are many such manufacturer names.
I tried creating a conversion table so that VISHAY in data set#1 can be translated to “VISHAY SPRAGUE” but it didn’t work very well because for some records both data set#1 and data set#2 may have “VISHAY”. In those cases my conversion would mark those actually matches as mismatches.
Sorry for this long confusing message; but, such is the problem.
BY the way I am using ACCESS 2007 and using SQL for writing scripts.
Thanks
Rajesh

 

[guerillaunit]

If it's a 1 to 1 conversion (where one name from data set #1 translates to one name from data set #2), you will be able to use a reference table. Create 2 columns on your table, the first column with the manufactures' names from data set #1 and the send column with the manufactures' names from data set #2. Link data set #1 to field 1 and data set #2 to field 2.

If there can be multiple versions of a name on data set #1 or data set #2, then bring both tables into design view without linking them. Double click the manufactures' names columns from both tables so they will show up in the results. Create a new column that uses an if statement to compare the shorter version of the name to the longer version of the name (you will need to use the wildcard character "*").
ie. If([data set #1].[name] like "*" & [data set #2].[name] & "*", "Match", "No Match")
In the above case, data set #1 has the shorter name

 

[22strider]

Thanks for your message. The option 2 in your message is what am I using right now. The problem is that the shorter version can be in data set 1 or 2.
I was thinking if there was a way of listing shorter versions of the manufacturers' names in a separate table (VISHAY for example) and then I could compare both the manufacturers' names with that shorter version table (using wild card) and if they both match then I could call it a match. What do you think? I'll give it a try and post my findings.
Thanks
Rajesh

 

[guerillaunit]

You can do 2 checks, the 1st check would check field 1 against field 2 and the 2nd check would check field 2 against field 1. If there's a match with either checks, you would have a positive identification

 

[xenou]

I would probably create a master table of all names mapped to a standardized name:

CompanyID   CompanyName
---------   -----------
Vishay      Vishay
Vishay      Vishay Sprague
Company A   Company A
Company A   Co. A
Company B   Company B
Company C   Company C

This is pretty much done by

1. appending all names from the first table to a new table that is indexed for uniques on company ID (two fields, CompanyID and CompanyName). So you get all the names from table 1.
2. Then update the second column to equal this same name (you have to start somewhere).
3. Now append the names from the second table to column B.
4. Then sort by column B, and start filling in the blanks in column A. and correcting the mistakes in column A.
5. If you have a lot of names it will take a while but this master list will be useful if this is an ongoing thing.

This mapping table is now used in all your queries to resolve the names to a single standard name (aka an ID).

 

[22strider]

Hello Xenou

Thanks for your message. I am trying to work through the process. Unfortunately I haven't been able to devote much time lately. I'll finish it shortly and post my feedback.

Thanks
Rajesh