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
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.