Join using LIKE

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract another
field. Is this possible? And is it possible without using VBA using a
Design View query?

Many thanks.

JE wrote:

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract
another
field. Is this possible? And is it possible without using VBA using a
Design View query?

It is possible and you do not need to use VBA, but you do have to use the
SQL view of the query designer. The graphical designer can only deal with
joins using =.

SELECT * FROM [Table 2]
INNER JOIN [Table 1]
ON [Table 2].FieldName LIKE [Table 1].FieldName & "*"

JE -

You can do this in query design. First, remove any links between the
tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from Table 2 put
criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

--
Daryl S


"JE" wrote:

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract another
field. Is this possible? And is it possible without using VBA using a
Design View query?

Many thanks.

=?Utf-8?B?RGFyeWwgUw==?= wrote in
:

You can do this in query design. First, remove any links between
the tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from
Table 2 put criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

Why do you need it under both fields? Certainly if you put that
criteria under Field1, all records will match, so it seems redundant
to me. It won't change the result set, but it might cause the query
optimizer to evaluate it incorrectly.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

If you were unsure of the direction of the match. One problem with this
approach is that if there is a null in field1 in either table then you are
going to end up matching every record (except those with nulls in field1).

There is a way around this, but the best solution is probably to use a
non-equi join as described elsewhere.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:
=?Utf-8?B?RGFyeWwgUw==?= wrote in
:

You can do this in query design. First, remove any links between
the tables, so they look like they are not joined at all.

Then, add the fields in the grid, and under the two fields from
Table 2 put criteria like this:
Like [Table 1]![field1] & "*"

Do this for both fields.

Why do you need it under both fields? Certainly if you put that
criteria under Field1, all records will match, so it seems redundant
to me. It won't change the result set, but it might cause the query
optimizer to evaluate it incorrectly.

John Spencer wrote in
:

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

I think the original question was quite clear that the match was in
one direction, as it said:

I have two tables with data that is nearly exact - one table has
truncated data...

On the other hand, I was completely unable to decipher the sample
data provided in relation to that statement.

If you were unsure of the direction of the match. One problem
with this approach is that if there is a null in field1 in either
table then you are going to end up matching every record (except
those with nulls in field1).

There is a way around this, but the best solution is probably to
use a non-equi join as described elsewhere.

But a non-equi join works in only one direction, so the
corresponding implicit join using a WHERE clause would not be the
one with criteria on both fields, as you suggest.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I don't disagree. I was only pointing out a possible reason for testing both
directions.

I'm not sure that you could not use a non-equi join in both directions.

I would have to test whether or not this would work - don't have the time
right now. Hopefully, I will have some time tomorrow to satisfy my curiousity.

SELECT *
FROM Table1 INNER JOIN Table2
ON (Table1.Field1 Like Table2.Field1 & "*"
OR Table2.Field1 Like Table1.Field1 & "*")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:
John Spencer wrote in
:

Given the scenario you might want to put
Like [Table 1][Field1] & "*" under [Table 2][Field1]

And on the next criteria put
Like [Table 2][Field1] & "*" under [Table 1][Field1]

I think the original question was quite clear that the match was in
one direction, as it said:

I have two tables with data that is nearly exact - one table has
truncated data...

On the other hand, I was completely unable to decipher the sample
data provided in relation to that statement.

If you were unsure of the direction of the match. One problem
with this approach is that if there is a null in field1 in either
table then you are going to end up matching every record (except
those with nulls in field1).

There is a way around this, but the best solution is probably to
use a non-equi join as described elsewhere.

But a non-equi join works in only one direction, so the
corresponding implicit join using a WHERE clause would not be the
one with criteria on both fields, as you suggest.

If the short field is consistent in the number of characters you can use two
queries by creating a calculated field in the first and left function.
In the second query join on the calculated field.
--
Build a little, test a little.


"JE" wrote:

I have two tables with data that is nearly exact - one table has truncated
data:

Table 1
BARRICK GOLD CORP
DIREXION SHS ETF TR

Table 2
BARRICK GOLD CORP COM ISIN#
DIREXION SHS ETF TR LARGE CAP BULL 3X SHS

I am wanting to join these two tables on the these fields to extract another
field. Is this possible? And is it possible without using VBA using a
Design View query?

Many thanks.