Help with identifying possible duplicates

[kevin43]

I reaaly need some help with this. I'm trying develop a method for listing possible duplicate vendors. The problem is I have tons of legacy vendors that need to be mapped to the current ERP. I have been using multiple LIKE statements with the first 10 characters of the legacy vendors name against the current ERP vendors. Then I use the output listing to manually map the the two. This is fine for only 1000 vendors but I have over 13k unique legacy vendors. MS Access has a limit of 1026 characters in the critieria field. So I was wondering if someone had a better/smarter method to this madness?

Thanks

[MMcCarthy]

I reaaly need some help with this. I'm trying develop a method for listing possible duplicate vendors. The problem is I have tons of legacy vendors that need to be mapped to the current ERP. I have been using multiple LIKE statements with the first 10 characters of the legacy vendors name against the current ERP vendors. Then I use the output listing to manually map the the two. This is fine for only 1000 vendors but I have over 13k unique legacy vendors. MS Access has a limit of 1026 characters in the critieria field. So I was wondering if someone had a better/smarter method to this madness?

Thanks

Can you post the SQL of the query you are currently using.

Mary

[kevin43]

This is a sample. This query will run w/o issue but I need to include a heck more LIKE statements.

SELECT Apvenaddr.[VENDOR-LOC], Apvenaddr.[VENDOR-NAME], Apvenaddr.VENDORNAME, Apvenaddr.ADDR1, Apvenaddr.ADDR2, Apvenaddr.ADDR3, Apvenaddr.ADDR4, Apvenaddr.[CITY-ADDR5], Apvenaddr.[STATE-PROV], Apvenaddr.[POSTAL-CODE]
FROM Apvenaddr
WHERE (((Apvenaddr.VENDORNAME) Like "ADELEKEKAM*" Or (Apvenaddr.VENDORNAME) Like "ADELPHIAGR*" Or (Apvenaddr.VENDORNAME) Like "ADEPTMEDIN*" Or (Apvenaddr.VENDORNAME) Like "ADEZABIOME*" Or (Apvenaddr.VENDORNAME) Like "ADJUSTADRA*" Or (Apvenaddr.VENDORNAME)
ORDER BY Apvenaddr.VENDORNAME;

[MMcCarthy]

What's the table name and field name of the VendorName in the Legacy table.

[kevin43]

I'm using the legacy vendors to create the LIKE statements. I'm not linking the 2 tables. Should I? If you need a legacy table just use a bogus name [legacy_vendors].

[MMcCarthy]

I'm using LegacyVendors as the table name and LegacyName as the name of the Legacy Vendor. Now try the following:

SELECT Apvenaddr.[VENDOR-LOC], Apvenaddr.[VENDOR-NAME], Apvenaddr.VENDORNAME, Apvenaddr.ADDR1, Apvenaddr.ADDR2, Apvenaddr.ADDR3, Apvenaddr.ADDR4, Apvenaddr.[CITY-ADDR5], Apvenaddr.[STATE-PROV], Apvenaddr.[POSTAL-CODE]
FROM Apvenaddr, LegacyVendors
WHERE (((Apvenaddr.VENDORNAME) Like Left(LegacyVendors.LegacyName, 10) & '*'
ORDER BY Apvenaddr.VENDORNAME;
 

[kevin43]

With a few minor tweaks that script worked.

Thanks so much.

[MMcCarthy]

With a few minor tweaks that script worked.

Thanks so much.

You're welcome.