How can I do a "best match" association between text strings?

G

Guest

I would like to associate actual dialed telephone numbers in one table to a
country code/city code/mobile code name directory in another table by means
of finding a "best match" in the dialed telephone number string from the
left-most to right-most text position. All the data are in text format - not
numeric. I am just trying to identify the country/city/mobile destination of
each individual call in my telephone call records. I am using the Access
query function but it attempts to find an exact match for all 10 text
characters in Table 1 which I do not want - all I need is for the first few
characters in Table 1 to match those in Table 2 and return the related
country/city/mobile name that matches it best.

Example, for calls to France:

Table 1 - Our actual call records
Call #1: 33155557777
Call #2: 33612341234
Call #3: 33845458989
Call #4: 33785857878

Table 2 - My country/city/mobile reference table
33 - France
331 - France (Paris)
336 - France (mobile)

My desired result:
33155557777 - France (Paris)
33612341234 - France (mobile)
33845458989 - France
33785857878 - France

Appreciate any info you can share on how to accomplish this.
 
M

Michel Walsh

Hi,


Supply a "weight" with each solution:


33* France 1
331* France(Paris) 10
336* France(mobile) 9



With an inner join :

... ON x.TelNumber LIKE y.pattern

It will then be a matter to GROUP BY x.TelNumber, and to extract the maximum
y.Weight associated to it.

You can then use another inner join (implying that Group By query as a saved
query, as in http://www.mvps.org/access/queries/qry0020.htm ) to get the
descriptive banner associated to that optimum.


Hoping it may help,
Vanderghast, Access MVP
 
J

JohnFol

You cannot do this easily in 1 query, as you have to itterate through
multiple comparisons per row. Id suggest code, or using a function within
the query, for example

Select NumberDialled, BestMatch(NumberDialled) from Table 1


Function BestMatch(PhoneNumber as string) as string

dim strResponse as string
strResponse = "No Match"
dim n as integer
n = len(PhoneNumber)
do while n > 0 and strResponse = "No Match"
if DLookup("Country_City", "Table2", "DialPrefix Like '" &
Left(PhoneNumber, n) & "'")
n = n - 1
loop

BestMatch = strResponse
end function


This is untested but gives you the basic approach. For speed, change the
dlookup stuff for Recordset . .
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top