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.

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.

=?Utf-8?B?RGFyeWwgUw==?= <(E-Mail Removed)> wrote in
news:FE1ABC91-992C-46EB-A0DB-(E-Mail Removed):

> 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==?= <(E-Mail Removed)> wrote in
> news:FE1ABC91-992C-46EB-A0DB-(E-Mail Removed):
>
>> 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 <(E-Mail Removed)> wrote in
news:#(E-Mail Removed):

> 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 <(E-Mail Removed)> wrote in
> news:#(E-Mail Removed):
>
>> 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.
>

Ok. I had to try it and the query worked with the bi-directional join and
produced the expected results.

SELECT FAQ.fid, FAQ_BU.fid, FAQ.FSubject, FAQ_BU.fSubject
FROM FAQ INNER JOIN FAQ_BU
ON FAQ.fSubject LIKE FAQ_BU.fSubject & "*"
OR FAQ_BU.fSubject LIKE FAQ.fSubject & "*"
ORDER BY FAQ.FId, FAQ_BU.FID

If I wanted to get rid of directional duplicates I would need to add a where
clause. In this particular case
WHERE Faq.fid <= faq_BU.fid

Or
WHERE Faq.fid < faq_BU.fid if I wanted to eliminate matches where the fid
field matched and only end up with cases where the FID was different but the
Fsubject field was a match.

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

John Spencer wrote:
> 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

John Spencer <(E-Mail Removed)> wrote in
news:eEeZ#(E-Mail Removed):

> 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 & "*")

That would throw an error, because the tables have to be in the same
order in a Join statement. You could only do it with a second
instance of one of the tables.

And it's not what the original poster asked for, since it was made
quite clear that the field in one table was a truncated version of
the data in the same field in the other table.

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