Queries with partial matches between tables

[Guest]

I have four data sets that I must compare against each other for matches and
similarities. The data is license plate numbers, meaning aplha and numeric
chars, and each set has been keyed as it's own table. The 'umatched query
wizard' has helped me indentify exact matches between the tables and remove
them. My problem is that I am having no luck trying to build a partial match
query between two tables.

Example: One table may contain '123' the other '1234' this would be called
a match in our case. I'm trying to build a query that will identify these
partial matches as matches so that they to could be excluded.

Thanks, in advance, for any help on the matter.

 

[Guest]

Try this --
Put both tables in Make Table query. pull down the fields you want to check
for matches. Use this for criteria on the first table data column --
Like "*" & [SecondTable].[DataField] & "*"
Use this for criteria on the second table data column - put this criteria in
the second row --
Like "*" & [FirstTable].[DataField] & "*"

The new table will have the fields that match and then can be used in a
query and joined with the first and second table (the first query is not
updateable).

 

[John Spencer (MVP)]

What are the rules you are using? Can you state them in English?

For example,
It's a match if
--tableA.FieldA begins with the characters in TableB.FieldB
--tableA.FieldA Ends with the characters in TableB.FieldB
--tableA.FieldA is contained in TableB.FieldB (which is a superset of the above
two items)
--tableB.FieldB begins with the characters in TableA.FieldA
....

You might be able to do this with a non-equi join for one set of criteria at a time.

SELECT TableA.FieldA
FROM TableA INNER JOIN TableB
ON TableA.FieldA LIKE TableB.FieldB & "*"

Should give you matches for the fourth condition listed above.

Another untested idea would be to use a Cartesian join of the two tables (No
join) and multiple where conditions

SELECT *
FROM TableA, TableB
WHERE TableA.FieldA = TableB.FieldB
OR TableA.FieldA LIKE TableB.FieldB & "*"
OR TableA.FieldA LIKE "*" & TableB.FieldB
OR TableB.FieldB LIKE "*" & TableA.FieldA & "*"
....

 

[Guest]

Karl,

Thanks for the assistance... you are spot on with your suggestions. The
query is returning the information correctly however I was wondering if it
possible to limit the characters that the query compares. Meaning...

Table 1 Table 2
123 1234
5123

The query as you had suggested returns both values (1234, 5123) as a match,
however I was hoping to have it only compare the first three characters of
the string. This would eliminate 5123 as a match as 123 are not the leading
characters.

I have done some research into the use of astericks (*), and this seems to
be what is allowing match in the leading, ending, or middle of string cases.
Is there a character that I can use in place of the * to have it limit the
matches to the leading three characters?

Thank you much for all of the great help to this point...

Try this --
Put both tables in Make Table query. pull down the fields you want to check
for matches. Use this for criteria on the first table data column --
Like "*" & [SecondTable].[DataField] & "*"
Use this for criteria on the second table data column - put this criteria in
the second row --
Like "*" & [FirstTable].[DataField] & "*"

The new table will have the fields that match and then can be used in a
query and joined with the first and second table (the first query is not
updateable).

I have four data sets that I must compare against each other for matches and
similarities. The data is license plate numbers, meaning aplha and numeric
chars, and each set has been keyed as it's own table. The 'umatched query
wizard' has helped me indentify exact matches between the tables and remove
them. My problem is that I am having no luck trying to build a partial match
query between two tables.

Example: One table may contain '123' the other '1234' this would be called
a match in our case. I'm trying to build a query that will identify these
partial matches as matches so that they to could be excluded.

Thanks, in advance, for any help on the matter.

 

[Guest]

Yep!

Like "*" & Left([SecondTable].[DataField],2) & "*"
Use this for criteria on the second table data column - put this criteria in
the second row --
Like "*" & Left([FirstTable].[DataField],3) & "*"

Karl,

Thanks for the assistance... you are spot on with your suggestions. The
query is returning the information correctly however I was wondering if it
possible to limit the characters that the query compares. Meaning...

Table 1 Table 2
123 1234
5123

The query as you had suggested returns both values (1234, 5123) as a match,
however I was hoping to have it only compare the first three characters of
the string. This would eliminate 5123 as a match as 123 are not the leading
characters.

I have done some research into the use of astericks (*), and this seems to
be what is allowing match in the leading, ending, or middle of string cases.
Is there a character that I can use in place of the * to have it limit the
matches to the leading three characters?

Thank you much for all of the great help to this point...

Try this --
Put both tables in Make Table query. pull down the fields you want to check
for matches. Use this for criteria on the first table data column --
Like "*" & [SecondTable].[DataField] & "*"
Use this for criteria on the second table data column - put this criteria in
the second row --
Like "*" & [FirstTable].[DataField] & "*"

The new table will have the fields that match and then can be used in a
query and joined with the first and second table (the first query is not
updateable).

I have four data sets that I must compare against each other for matches and
similarities. The data is license plate numbers, meaning aplha and numeric
chars, and each set has been keyed as it's own table. The 'umatched query
wizard' has helped me indentify exact matches between the tables and remove
them. My problem is that I am having no luck trying to build a partial match
query between two tables.

Example: One table may contain '123' the other '1234' this would be called
a match in our case. I'm trying to build a query that will identify these
partial matches as matches so that they to could be excluded.

Thanks, in advance, for any help on the matter.