partial text match

[TimkenSteve]

Could anyone direct me to a source for this problem?
I have two tables where 1 one field must be compared with 1 field in
the other table. One table is the set of all end users allowed a
discount by our company the other is the end users our customer is
attempting to give a discount. Not all of the end users from table 1
will match end users in table 2. And, the names may be of different
case, some may be abbreviated ect. The only thing I have going for me
is they are linked by a CATS number.
The below is a make table query bringing the two fields required to be
compared next to each other
BY CATS

CATS EUtable1 EUtable2
Audit Result
1002 BOBS TOWING Bob's Towing
1007 LTD LTD Services Corp
1002 Simpco Toring and Simpco
1008 Barnes Inc Bobs Towing
Invalid End User
1271 GMPlant general motors

Can a match text query bounce CATS 1008 and ignore the rest? GM general
motors probably is the deal breaker.
Any Ideas
Steve

 

[Guest]

Steve:

Probably the closest you could get would be to see if any 'word' from table
1 is a substring in table 2 where the CATS numbers match. You'd first need
to put a function like this in a standard module:

Public Function MatchWords(str1 As String, str2 As String) As Boolean

Dim strWord As String
Dim intSpacePos As Integer

' parse string 1 and determine if each word
' is a substring in string 2
str1 = str1 & " "
strWord = str1
Do While strWord <> ""
intSpacePos = InStr(1, LTrim(str1), " ")
strWord = Left(str1, intSpacePos - 1)
If InStr(str2, strWord) > 0 Then
'match found so return True
MatchWords = True
Exit Function
Else
str1 = Mid(str1, intSpacePos + 1)
If str1 = "" Then Exit Function
End If
Loop
End Function

The call in in a query:

SELECT Eutable1.CATS, Eutable1.EndUser, Eutable2.EndUser
FROM Eutable1, Eutable2
WHERE Eutable1.CATS = Eutable2.CATS
AND NOT MatchWords(Eutable1.EndUser, Eutable2.EndUser);

This would return 1008, but would of course also return 1271 General Motors.
The only way you could cater for that, I think, would be to create a
'Translation' table with pairs of the values used in each table in columns
EndUser1 and EndUser2 ( so it would have rows with every permutation of valid
end user names). Make the two columns the composite primary key of the table.

You could then include that in a subquery:

SELECT Eutable1.CATS, Eutable1.EndUser, Eutable2.EndUser
FROM Eutable1, Eutable2
WHERE Eutable1.CATS = Eutable2.CATS
AND NOT EXISTS
(SELECT *
FROM Translation
WHERE Translation.EndUser1 = Eutable1.EndUser
AND Translation.EndUser2 = Eutable2.EndUser);

Probably the best way to populate the Translation table would be to fill it
with all the pairs from the existing tables then manually delete the unwanted
rows. You can fill it with an 'append' query:

INSERT INTO Translation (EndUser1, EndUser2)
SELECT Eutable1.EndUser, Eutable2.EndUser
FROM Eutable1 INNER JOIN Eutable2
ON Eutable1.CATS = Eutable2.CATS;

If there are duplicated pairs in the original tables only one of each will
be appended by virtue of the two columns being the primary key of Translation.

Ken Sheridan
Stafford, England