Newbie that needs help

[Alistair]

Ok then heres what i think is a complex one.

Table 1
Field1 Field2 Field3
077473837 106 1234
0774 105 1234
07 104 1234

Table 2
Field1
077473
078900
077400

I want to match field1 in table 2 to the best match (most digits matched) in Field1 of table 1 to give Table 2 Fields 2 and 3

So as per the example the results would be

Field1 Field2 Field3
077473 106 1234
078900 104 1234
077400 105 1234

But obvioulsy in a table with thousands of records....

Cheers,
Alistair

 

[khawar]

What is the criteria for matching

 

[Atomic Shrimp]

This function will tell you the number of common, sequential digits there are at the start of two strings

Public Function CommonDigits(string1, string2 As String) As Integer
Dim intloop As Integer
intloop = 1
Do While Mid(string2, intloop, 1) = Mid(string1, intloop, 1)
intloop = intloop + 1
Loop
CommonDigits = intloop - 1
End Function

So if [field1] contains "abcdefg" and [field2] contains "abzcdefg", CommonDigits([field1],[field2]) will return 2

To use the function, paste it into a new module in your database, then use it just as you would any of the built in functions.

It should then be possible to build a query selecting only the highest numbered CommonDigits result for each of your inputs, however....

What do you want it to do if there are two or more matches, each as good as one another?

 

[ByteMyzer]

This can be done without a custom function. Try the following SQL syntax (substitute highlighted text with actual table/field names):

SELECT T2.[b][i]Field1[/i][/b], T1.[b][i]Field2[/i][/b], T1.[b][i]Field3[/i][/b]
FROM [b][i]Table1[/i][/b] AS T1, [b][i]Table2[/i][/b] AS T2
WHERE T1.[b][i]Field1[/i][/b] =
(SELECT TOP 1 T3.[b][i]Field1[/i][/b]
 FROM [b][i]Table1[/i][/b] AS T3
 WHERE InStr(1, T2.[b][i]Field1[/i][/b], T3.[b][i]Field1[/i][/b]) = 1
 OR InStr(1, T3.[b][i]Field1[/i][/b], T2.[b][i]Field1[/i][/b]) = 1
 ORDER BY LEN(T3.[b][i]Field1[/i][/b]) DESC
);