match middle initial with middle name field

[cindy]

Hi -
I have two querries that are being joined by student IDs; some of the
querries have middle itintials and some middle name... IF we have First and
last exactly the same but one middle is A and one is Ann -- I get 2 different
entires.
Is there a way to match Mary A Jones and Mary Ann Jones as duplicates???
We need the full middle name to show and the other to disappear. They
would have Identical student ID's.
So... if ID#'s match show Ann not hide A - seems like a logical thing to
do, but I'm just not getting it to work..
Any ideas would sure be appreciated!!!!

thanks in advance for your help!!!
Cindy

 

[John W. Vinson]

Hi -
I have two querries that are being joined by student IDs; some of the
querries have middle itintials and some middle name... IF we have First and
last exactly the same but one middle is A and one is Ann -- I get 2 different
entires.
Is there a way to match Mary A Jones and Mary Ann Jones as duplicates???
We need the full middle name to show and the other to disappear. They
would have Identical student ID's.
So... if ID#'s match show Ann not hide A - seems like a logical thing to
do, but I'm just not getting it to work..
Any ideas would sure be appreciated!!!!

thanks in advance for your help!!!
Cindy

SELECT FirstName, IIF(TableA.MiddleName = Left(TableB.MiddleName, 1) AND
Len(TableB.MiddleName) > 1, TableB.Middlename, IIF(TableB.MiddleName =
Left(TableA.MiddleName, 1) AND Len(TableA.MiddleName) > 1, TableA.Middlename),
TableA.MiddleName) AS TheMiddleName;


John W. Vinson [MVP]

 

[cindy]

Thanks John....
Perfect - thanks for your help!!!
Cindy