Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    9

    Unanswered: Trouble with INNER JOIN

    Hi,

    I'm using LEFT JOIN in a SELECT statement (ACCESS 2013 using ADO with VBA) and not getting the result I expect.

    Base query is below (no table joins at all):

    SELECT A.f2, A.f3, A.f4
    FROM TBL AS A
    WHERE A.f0='xxx'
    AND A.f1 IS NOT NULL
    AND ISDATE(A.f3)

    Result is 118530 records.

    What I'm trying to do is select all records that result from the base query but add another field that results from a match in another dB where such match exists. My understanding of LEFT JOIN does just that (blank record where there is no match). However, when I execute the below I get more records than the base query, which isn't what I thought should happen. Seems to me the number of records should be identical as the base query.

    SELECT A.f2, A.f3, A.f4, B.f2
    FROM TBL AS A
    LEFT JOIN [C:AccessDB.accdb].T2 AS B
    ON A.f4=B.f1
    WHERE A.f0='xxx'
    AND A.f1 IS NOT NULL
    AND ISDATE(A.f3)

    Result is 119350 records.

    Either I'm doing something wrong or don't understand what LEFT JOIN does. Any tips or comments would be welcome. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    first I would Just link said table into current DB

    then Use the "Query Design" drag and drop the Feilds so they create a link
    then right click on the Black line and click "Join Properties" then select the Right option 2,3
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Jan 2015
    Posts
    9
    I'm using VBA, not the GUI. Maybe I posted in the wrong sub-forum?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Quote Originally Posted by ken18 View Post
    I'm using VBA, not the GUI. Maybe I posted in the wrong sub-forum?
    When I do VBA projects I still use ms access to get the SQL statement right
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    With regards to LEFT JOINT

    In your posted query all the records will be returned from Table A and blanks from table B if A.f4 is not in B.f1.

    However, just like an INNER JOIN, if a value A.F4 occurs more than once in table B.f1 a record will be returned for every instance of B.f1, duplicating the record A.f4 in table A.

    Hope that makes some kind of sense.


    MTB

  6. #6
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    188
    LEFT JOIN: Take all records from the left Table and JOIN to the Table at the Right, display matching record values at right table fields, along with values of left-table, otherwise show blanks at the right side table fields of record.

    Check which Table have maximum records, place that table at the LEFT side or go for a RIGHT JOIN.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  7. #7
    Join Date
    Apr 2017
    Posts
    14
    It would also be helpful if you told us how many records there are in the tbl and t2 tables before running the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •