Link part of a field in a query

  • Thread starter Thread starter Tony Delahaye
  • Start date Start date
T

Tony Delahaye

I have a field imported from a fixed width file called
Name (eg Delahaye Tony) Some of the records have middle
names aswell.

I need to link to another table which has field called
Surname (eg Delahaye) and Field First Name (eg Tony or T.

I have different information on each table that I would
like to combine where the surname matches.

How could I match on the surname? Any ideas?

Preference is to perform task in Access rather than EXCEL.

Thanks
TD
 
Hi Tony,

At a guess there are spelling errors too, plus instances of 2 or more
people with the same name (Smith J), so you're unlikely to achieve 100%
accuracy. And if the Name field contains not just
Delahaye Tony
and
Kennedy John Fizgerald
but also
Boutros Ghali Boutros
and
Van Der Westhuizen Jan Pieter
programming becomes impossible.

With that proviso, here are some things you can do.

Create a query on the first table with a calculated field like this:
Surname: IIF(IsNull([Name]), Null, Left([Name], Instr([Name]," ")
-1)

That gives you a surname field you can join to the surname field in the
other table. (Except that it fails if the surname consists of more than
one word.)

For FirstName, the starting point is something like
FirstName: IIF(IsNull([Name], Null, Mid([Name], Instr([Name," ") + 1
but again multi-word surnames and middle names complicate matters.

A more effective though still imperfect approach is to use specialist
software. http://www.infoplan.com.au have products called Splitter and
NameParser which seem to do a decent job on ordinary "western" names,
certainly a better job than most of us could do without weeks of hard
programming.
 
Back
Top