Results 1 to 5 of 5

More than expected records when joining two tables

  1. #1
    shabar is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    66

    More than expected records when joining two tables

    Hi All


    I have following two tables


    Table 1
    =======


    T1Col1 T1Col2 T1Col3


    jkl nnn qoq
    efg Aaa wow
    efg xxx yoy
    abc iii qoq
    abc fff wow
    abc rrr qoq
    gdg ppp yoy




    Table 2
    =======


    T2Col1 T2Col2


    qoq nnn
    wow aaa
    yoy xxx




    Link column between two table is T1.T1Col3 and T2.T2Col1


    Expected Results
    ================


    T1Col1 T1Col2 T1Col3 T2Col2


    jkl nnn qoq nnn
    efg Aaa wow aaa
    efg xxx yoy xxx
    abc iii qoq nnn
    abc fff wow aaa
    abc rrr qoq nnn
    gdg ppp yoy xxx



    With join query I am getting multiple records. But I am after the above no of records ONLY. Because
    In my real scenario Table 1 got more than million records. So ultimate result set records count is
    huge which exceeds MS ACCESS limitation




    Is this possible


    Cheers

    Shabar



  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,609
    Makes me think T2Col1 values are not unique. Should they be?

    Show the query SQL statement.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    shabar is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    66
    Yes you are correct June7, T2Col1 has duplicate records. Following is the query






    SELECT T1Col1, T1Col2, T1Col3, T2Col2
    FROM
    (SELECT DISTINCT T1Col1, T1Col2, T1Col3 FROM Tab1) AS T1
    LEFT JOIN (SELECT DISTINCT T2Col1 T2Col2 FROM Tab2) AS T2 ON
    T1.T1Col3=T2.T2Col1
    WHERE T2.T2Col1 IS NOT NULL;

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,609
    Then the query is acting correctly.

    Perhaps you need a compound join.

    SELECT T1.*, T2.* FROM T1 LEFT JOIN T2 ON
    T1.Col3=T2.Col1 And T1.Col2=T2.Col2
    WHERE T2.Col1 Is Not Null;
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    shabar is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    66
    Yes June7

    As you recommend may be needs to create another key to get rid of duplicate issue. If not possible has to find a way out to reduce the number of records

    Thax for your suggestion


    Cheers

    Shabar

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-21-2012, 11:21 PM
  2. Options when joining two tables
    By Kevin_ in forum Queries
    Replies: 9
    Last Post: 07-29-2011, 09:06 PM
  3. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 12:45 PM
  4. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 03:37 AM
  5. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 02:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums