PC Review


Reply
Thread Tools Rate Thread

Join using LIKE

 
 
JE
Guest
Posts: n/a
 
      22nd Feb 2010
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.
 
Reply With Quote
 
 
 
 
Rick Brandt
Guest
Posts: n/a
 
      22nd Feb 2010
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 & "*"

 
Reply With Quote
 
 
 
 
Daryl S
Guest
Posts: n/a
 
      22nd Feb 2010
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.

 
Reply With Quote
 
KARL DEWEY
Guest
Posts: n/a
 
      22nd Feb 2010
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.

 
Reply With Quote
 
David W. Fenton
Guest
Posts: n/a
 
      23rd Feb 2010
=?Utf-8?B?RGFyeWwgUw==?= <(E-Mail Removed)> wrote in
news:(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/
 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      23rd Feb 2010
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:(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.
>

 
Reply With Quote
 
David W. Fenton
Guest
Posts: n/a
 
      23rd Feb 2010
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/
 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      24th Feb 2010
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.
>

 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      24th Feb 2010
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

 
Reply With Quote
 
David W. Fenton
Guest
Posts: n/a
 
      25th Feb 2010
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/
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Join tables like inner join in Access ryanp Microsoft Excel Misc 2 18th Jul 2008 03:35 PM
Quake 4 is out and it's a game like doom 3. And i would say it like doom... hahaha what a joke also the beginning is like pariah and halo 1 ... coping is lame DDC ATI Video Cards 18 1st Nov 2005 01:03 AM
Left Join acting like inner join??? =?Utf-8?B?VmFjYXRpb24ncyBPdmVy?= Microsoft Access VBA Modules 7 10th Jun 2005 08:35 PM
Poll Question : How well do you like this Father's Love Letter video presentation? (A.) I like it. (B.) I don't like it. (C.) I like it so much that it made me cry. IBMclerk7781226@yahoo.com Windows XP General 3 18th Apr 2005 02:30 AM
cross join? full join? or something like that? Alejandra Parra Microsoft Access Queries 1 17th Jun 2004 06:22 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:08 AM.