comparing two tables for similar data

[Tina]

I need to compare two tables for similarites. I have my supplier list that I
need to compare to the Government excluded parties list. If one of my
suppliers is on the Gov't list, I can't do business with them. I'm running
into a few problems....

-My supplier list has 750 records that I need to compare to the Gov't list
of 50K+ records. Is there a way to automate this so I don't have to enter
each of my records individually to compare them to the Gov't database?

- My supplier list does not match exactly the Gov't company list (ie. my
company might be listed as Jane Smith and Company whereas on the Gov't list
it might be listed as Jane Smith Co. or Smith, Jane & Comp.

Thanks in advance!!

Tina

 

[Tom Ellison]

Dear Tina:

Where the names match exactly, this isn't much of a problem. Use a
criterion like:

WHERE SupplierList.CompanyName NOT IN (SELECT CompanyName FROM
GovenrmentList)

Where they may not be spelled exactly identically, I'm not sure I could
reliably tell myself whether someone is excluded or not. Indeed, even if
spelled identically, I could not say for certain someone is excluded. Is
there no possibility of there being two Jane Smiths who have companies, and
call themselves the same?

Unless the companies use a Federal Employer ID or some similar unambiguous
and unique key, I don't see how it is possible, whether done on a computer
or by a person manually. Perhaps you should ask the Feds just how this is
expected to be accomplished reliably.

If the Feds supply a set of rules for making this comparison reliably,
perhaps that can be accomplished. If not, I don't see how you are going to
be able to do this.

Tom Ellison

 

[Tina]

Tom -

Thanks for the quick reply. If one of my suppliers is on the Gov't list, it
just means I need to do more investigating to make sure they're the exact
same company on the Gov't list before I exclude them.

I figured out how to do a quick parameter query so that if I enter "smith",
it will bring back all the "smith"s so I don't need to worry if a name
doesn't exactly match. But here's my dilemma.....With my parameter query, I
need to search all 750 of my suppliers individually. (on a monthly basis).
Is there a way for me to automate this query? so it either looks up all my
suppliers all at once or maybe even 50 at a time? I'm not very proficient at
vb so the simpler the better.

Thanks!!

 

[John Vinson]

I figured out how to do a quick parameter query so that if I enter "smith",
it will bring back all the "smith"s so I don't need to worry if a name
doesn't exactly match. But here's my dilemma.....With my parameter query, I
need to search all 750 of my suppliers individually. (on a monthly basis).
Is there a way for me to automate this query? so it either looks up all my
suppliers all at once or maybe even 50 at a time? I'm not very proficient at
vb so the simpler the better.

This is just BARELY possible, although it gets really snarky. What
you'ld need to do is parse out each word in your company name field,
exclude common words like "The", "Inc.", "Co.", "Corp." etc., and
search using LIKE. This could be done in VBA code but I don't know if
it's possible using a query.

John W. Vinson[MVP]