Join using LIKE

Discussion in 'Microsoft Access Getting Started' started by JE, Feb 22, 2010.

  1. JE

    JE Guest

    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, Feb 22, 2010
    #1
    1. Advertisements

  2. JE

    Rick Brandt Guest

    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 & "*"
     
    Rick Brandt, Feb 22, 2010
    #2
    1. Advertisements

  3. JE

    Daryl S Guest

    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.
     
    Daryl S, Feb 22, 2010
    #3
  4. JE

    KARL DEWEY Guest

    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.
     
    KARL DEWEY, Feb 22, 2010
    #4
  5. =?Utf-8?B?RGFyeWwgUw==?= <> wrote in
    news::

    > 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/
     
    David W. Fenton, Feb 22, 2010
    #5
  6. JE

    John Spencer Guest

    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
    > news::
    >
    >> 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, Feb 23, 2010
    #6
  7. John Spencer <> wrote in
    news:#:

    > 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/
     
    David W. Fenton, Feb 23, 2010
    #7
  8. JE

    John Spencer Guest

    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
    > news:#:
    >
    >> 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.
    >
     
    John Spencer, Feb 24, 2010
    #8
  9. JE

    John Spencer Guest

    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, Feb 24, 2010
    #9
  10. John Spencer <> wrote in
    news:eEeZ#:

    > 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/
     
    David W. Fenton, Feb 25, 2010
    #10
  11. John Spencer <> wrote in
    news::

    > FROM FAQ INNER JOIN FAQ_BU
    > ON FAQ.fSubject LIKE FAQ_BU.fSubject & "*"
    > OR FAQ_BU.fSubject LIKE FAQ.fSubject & "*"


    I was under the impression that have a join statement with the
    tables in opposite order in the conditions doesn't work. I'm too
    tired to test it, though.

    I still can't quite figure out the utility of such a thing, though.
    It certainly doesn't fit the original poster's scenario, which was
    one that makes perfect sense to me.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/
     
    David W. Fenton, Feb 25, 2010
    #11
  12. JE

    John Spencer Guest

    It does work. And yes we have gone far astray of the original poster's
    request. I think we can terminate this discussion.

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

    David W. Fenton wrote:
    > John Spencer <> wrote in
    > news:eEeZ#:
    >
    >> 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.
    >
     
    John Spencer, Feb 25, 2010
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. eb1mom

    self join

    eb1mom, Mar 17, 2004, in forum: Microsoft Access Getting Started
    Replies:
    6
    Views:
    198
    eb1mom
    Mar 18, 2004
  2. Guest

    How many table can join by using Ms Access Query?

    Guest, Mar 23, 2004, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    321
    Cheryl Fischer
    Mar 23, 2004
  3. Chris

    Cannot enter value into blank field on 'one' side of outer join

    Chris, Aug 16, 2004, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    736
    Chris
    Aug 16, 2004
  4. Rasoul Khoshravan Azar

    Difference of join in query & relationship

    Rasoul Khoshravan Azar, Feb 27, 2005, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    1,128
    Guest
    Feb 28, 2005
  5. Chris

    Simple non-equi Join query

    Chris, May 11, 2005, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    267
    LeAnne
    May 12, 2005
Loading...

Share This Page