Joining Fields With Similar Data (But Not Exactly Matching Data)

[neilisou]

Hi, I'm hoping someone out there knows the answer to this one!

I've currently got two tables that I'm trying to join. However the
two fields that should be joined contain slightly different
information, i.e.:

Table1.ChkName might contain Smith Mr A

and

Table2.ChkName might contains Smith Mr AR

and I want to match these to return data on this basis from
Table2.DeptCode

Is there any way that I can use something similar to a "like" command
to return the nearest matches from each table? Similar to using the
"1" flag at the end of a VLOOKUP to return the nearest approximate in
excel?

Any help that anyone can give on this would be much appreciated!

Thanks,

Neil

 

[doco]

LIKE "*MrA*"

or if you are referencing a form control

LIKE "*" & Forms!myForm!myControl & "*"

HTH

 

[neilisou]

LIKE "*MrA*"

or if you are referencing a form control

LIKE "*" & Forms!myForm!myControl & "*"

HTH

Thanks Doco,

Unfortunately I am aware of the LIKE "*Mr A*" command what I'm looking
to do is make the connection dynamic so something along the lines of:

SELECT Table2.DeptCode
WHERE Table1.ChkName LIKE Table2.ChkName

etc etc...

Sorry to be a pain! Is this possible?

Neil

 

[Michel Walsh]

You can. If the wildcard is not part of the value in the field, you have to
add it:


SELECT table2.*
FROM table1 INNER JOIN table2
ON table1.Field1 LIKE "*" & table2.Field1 & "*"


Sure, if you have "Smith Mr A." and "Smith Mr A AR", no match will be
found between these two.

On the other hand, you can supply your own VBA subroutine, as long as it
returns true, or false (and it is public, in a standard module) :



SELECT table2.*
FROM table1 INNER JOIN table2
ON MyFunctionAboutAreTheArgumentsLookAlike( table1.Field1,
table2.Field1 )





Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Well, you could use something like the following to get some matches

SELECT Table2.DeptCode
FROM Table2, Table1
WHERE Table1.ChkName LIKE Table2.ChkName & "*"
OR Table2.ChkName LIKE Table1.ChkName & "*"

Or if the beginning of Table2.ChkName matched Table1.ChkName

SELECT Table2.DeptCode
FROM Table2 INNER JOIN Table1
ON Table2.ChkName LIKE Table1.ChkName & "*"

Both of those would produce FALSE matches if the ChkName after the LIKE
operator was null, but you could screen those out using
WHERE Table1.ChkName is not null and Table2.ChkName is Not Null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[neilisou]

Thanks, this has really helped! Can't believe I couldn't figure out
the syntax for that, what a school boy error!

Thanks again,

Neil