By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

Help with identifying possible duplicates

P: 4
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
Mar 1 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Mar 1 '07 #2

P: 4
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;
Mar 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
What's the table name and field name of the VendorName in the Legacy table.
Mar 1 '07 #4

P: 4
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].
Mar 1 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm using LegacyVendors as the table name and LegacyName as the name of the Legacy Vendor. Now try the following:

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM Apvenaddr, LegacyVendors
  3. WHERE (((Apvenaddr.VENDORNAME) Like Left(LegacyVendors.LegacyName, 10) & '*'
  4. ORDER BY Apvenaddr.VENDORNAME;
  5.  
Mar 1 '07 #6

P: 4
With a few minor tweaks that script worked.

Thanks so much.
Mar 1 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
With a few minor tweaks that script worked.

Thanks so much.
You're welcome.
Mar 1 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.