Comparing two fields in different tables

  • Thread starter Thread starter Pete Provencher
  • Start date Start date
P

Pete Provencher

Using Access 2000:

I have two tables that have owner names. i would like to write a query that
will tell me the differences. I creaed this query:

Diff: IIf([data].[ownername]=[county].[owner],"same","diff")

The problem is that the [county] table has a comma between the last and
firist name and it also might have the middle name spelled out. Is there a
way to use "like" so it will find something close.

Thanks for any assistance.

Pete Provencher
 
Using Access 2000:

I have two tables that have owner names. i would like to write a query that
will tell me the differences. I creaed this query:

Diff: IIf([data].[ownername]=[county].[owner],"same","diff")

The problem is that the [county] table has a comma between the last and
firist name and it also might have the middle name spelled out. Is there a
way to use "like" so it will find something close.

Thanks for any assistance.

Pete Provencher
Click to expand...

This can be very difficult. Is "Bob Smith" the same person as "Smythe,
Robert A."? Even if the names match, is the Bob Smith in [Data] the
same person as the Bob Smith in [County]? Names are not unique;
without more information it may be impossible to tell (just ask the
election commissioners in Florida trying to exclude felons from the
voter rolls).

I would suggest - for starters - adding Firstname, Lastname and
Middlename fields to both tables, and running Update queries to
populate them. For the table with commas, e.g. "Smith, Robert A.", you
can update LastName to

Left([owner], InStr([owner], ",") - 1)

and First to

Trim(Mid([owner], InStr([owner], ",") + 1)

followed by a second update query moving the middle initial/name (if
any) to the middlename field using similar logic. You can then get a
*rough* match by creating a Query joining the two tables (on some
other field - address perhaps??) and using an expression like

Match: [Data].[LastName] = [County].[LastName] AND
Left([Data].[Firstname], 3) = Left([County].[Firstname], 3)

ignoring middle names for now since they are notoriously misentered
and/or unreported.

Perhaps the best solution is to create a report joining the two tables
and sorting by lastname and firstname and manually checking those
records. The Match yes/no value will help narrow the field a bit, but
it WILL have false matches and it WILL miss true matches.
 
Pete

Would you consider the following to be "enough" alike that you'd expect
Access to find them?:

Pete Provenher
Peter Provencher
P. Provencher
Provencher, Peter J.
Provenher, P. J.
Provencher, Peter "John"

The "Like" operator looks for a string (say, like "oven"). You can also
specify a number of digits and/or characters for which any value would
match. But, given the examples above, how would you decide?! How would
Access decide??!!

Is there any chance you have any other characteristics to attempt a match
on? One approach might be to simply match on last names, sort on first and
middle, and "manually" (with your eyeballs and brains) determine what
actually IS a match.
 
Back
Top