Compare similar cells in access

[joe.anderson20]

I am trying to compare similar information between two access tables.
The information is in the form of numbers. In one table they can be
shown as similar to 0123456789 and the other table shows them as
456789. At any time the numbers can be shown in any number of digits,
but at least five will be the same.

Is there any way that I can set up the database to find the cells
which are similar?

 

[Douglas J. Steele]

You may need a more precise definition of "similar"

For the example you gave, you could use

WHERE Left(CStr(Nz([Table1].[Field1],"")), 5) =
Left(CStr(Nz([Table2].[Field1], "")), 5)

 

[joe.anderson20]

You may need a more precise definition of "similar"

For the example you gave, you could use

WHERE Left(CStr(Nz([Table1].[Field1],"")), 5) =
Left(CStr(Nz([Table2].[Field1], "")), 5)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I am trying to compare similar information between two access tables.
The information is in the form of numbers. In one table they can be
shown as similar to 0123456789 and the other table shows them as
456789. At any time the numbers can be shown in any number of digits,
but at least five will be the same.

Is there any way that I can set up the database to find the cells
which are similar?- Hide quoted text -

- Show quoted text -

The numbers in the first table are the 'true' numbers. The numbers in
the second table may have more digits than the first table, but will
contain at least five numbers in the same sequence as that of the
first table. I am trying to find a way to find which ones match.

 

[Ron2006]

When the second table has 5 that match,

Is it the first 5 or the last 5 or does it have to try to match 5 in
the middle?

Ron

 

[John W. Vinson]

I am trying to compare similar information between two access tables.
The information is in the form of numbers. In one table they can be
shown as similar to 0123456789 and the other table shows them as
456789. At any time the numbers can be shown in any number of digits,
but at least five will be the same.

Is there any way that I can set up the database to find the cells
which are similar?

Well... first off, spreadsheets have "cells"; Access databases don't.
Data is stored in Fields which make up Records in Tables.

What is the structure of your tables? Are you trying to match *any*
5-character substring of a particular field in one table, with *any*
5-character substring of a corresponding field in the other? Or are
there multiple fields in the table, any of which might match? Are
these Text type fields or Number? What constitutes "similar" - sharing
a five-digit substring, or is there some other measure of
"similarity"?

And... what real-life meaning do these numbers have? What's the
significance of the five digit string?

More info please!

John W. Vinson [MVP]

 

[John Nurick]

The numbers in the first table are the 'true' numbers. The numbers in
the second table may have more digits than the first table, but will
contain at least five numbers in the same sequence as that of the
first table. I am trying to find a way to find which ones match.

This still isn't quite precise enough. Do you mean that two records
match

a) if the last N digits in the value in the second table match the
digits in the first table, where N = the number of digits in the first
table. E.g.
123456 matches 5454123456

b) if the last 5 digits in the second table match the last 5 digits in
the first table. E.g.
123456 matches 6565623456

c) if the entire string of digits in the first table is *contained* in
the string of digits in the second table. E.g.
123456 matches 123456789, 5512345677 and 987123456

d) if any string of 5 digits in the first table is contained in the
string of digits in the second table. E.g.
123456 matches 7723456, 9812345677 and 1112345

c) something else?

 

[joe.anderson20]

The numbers in the first table are the 'true' numbers. The numbers in
the second table may have more digits than the first table, but will
contain at least five numbers in the same sequence as that of the
first table. I am trying to find a way to find which ones match.

This still isn't quite precise enough. Do you mean that two records
match

a) if the last N digits in the value in the second table match the
digits in the first table, where N = the number of digits in the first
table. E.g.
123456 matches 5454123456

b) if the last 5 digits in the second table match the last 5 digits in
the first table. E.g.
123456 matches 6565623456

c) if the entire string of digits in the first table is *contained* in
the string of digits in the second table. E.g.
123456 matches 123456789, 5512345677 and 987123456

d) if any string of 5 digits in the first table is contained in the
string of digits in the second table. E.g.
123456 matches 7723456, 9812345677 and 1112345

c) something else?

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.- Hide quoted text -

- Show quoted text -

Lets try again,

OK I have two tables containing a lot of information but I want to
compare two sets of records. The first table will have records of
numbers with up to, or more than than, twelve digits. The second table
will have records containing numbers with an undefined number of
digits, but within it there should be in the region of 5 digits which
match a sequence somewhere within the record in the first table. I
want to find the records which have no matches between the tables.

The numbers are phone numbers, but they do not come from one country.

 

[John W. Vinson]

Lets try again,

OK I have two tables containing a lot of information but I want to
compare two sets of records. The first table will have records of
numbers with up to, or more than than, twelve digits. The second table
will have records containing numbers with an undefined number of
digits, but within it there should be in the region of 5 digits which
match a sequence somewhere within the record in the first table. I
want to find the records which have no matches between the tables.

The numbers are phone numbers, but they do not come from one country.

So if you have Table1 with records

12345
23456
34567
111111
2222222

and Table2 with records

1234567000
1110111111
2222222111111
88888888

then the first record in Table2 should "match" each of the first three
records in Table1; the second record should match the 111111 record;
the third should match both that and the 2222222; and the fourth
record would be a "hit", not matching ANY of the five?

If so:

SELECT Table1.*
FROM Table1
LEFT JOIN Table2
ON Table2.Fieldname LIKE "*" & Table1.Fieldname & "*"
WHERE Table2.Fieldname IS NULL;

Don't expect this query to be lightning fast...!

John W. Vinson [MVP]

 

[John Nurick]

So if you have Table1 with records

12345
23456
34567
111111
2222222

and Table2 with records

1234567000
1110111111
2222222111111
88888888

then the first record in Table2 should "match" each of the first three
records in Table1; the second record should match the 111111 record;
the third should match both that and the 2222222; and the fourth
record would be a "hit", not matching ANY of the five?

If so:

SELECT Table1.*
FROM Table1
LEFT JOIN Table2
ON Table2.Fieldname LIKE "*" & Table1.Fieldname & "*"
WHERE Table2.Fieldname IS NULL;

This "matches" where the value in Table2 *contains* the value in Table1,
but if I read Joe right this is only a subset of what he wants:

I read this to mean that a "match" occurs when there is a string of "in
the region of 5 digits" that appears both in the Table1 value and the
Table2 value. LIKE won't help there, and nor will a regex engine. Maybe
something like this in a rather slow join?

Public Function JoesMatch001(S1 As Variant, S2 As Variant) As Variant
Dim j As Long
Dim Target As String

If IsNull(S1) Or IsNull(S2) Then
JoesMatch001 = Null
Exit Function
End If

If InStr(S2, S1) Then
JoesMatch001 = True
Exit Function
End If

For j = 1 To Len(S1) - 5
If InStr(S2, Mid(S1, j, 5)) Then
JoesMatch001 = True
Exit Function
End If
Next
JoesMatch001 = False
End Function