Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: Compare tables and display non-matching results

    Hi all,
    Good morning

    Someone PLEASE help me, I'm struck in a weird scenario in MS Access 2013 where,
    I'm trying to compare FileNames listed in Table_A and Table_B. The output should display the missing fileNames in Table_B
    In Table_A, the User is assigned the access to few files when compared to the complete list of files listed in Table_B

    I've tried using the "UnMatched query" in Access (with various combinations, Not NULL options), but it doesn't seem to work.

    =====================
    SELECT TableA.User, TableB.File
    FROM TableA RIGHT JOIN TableB ON TableA.[File] = TableB.[File]
    WHERE (((TableA.File) Is Null));
    =====================

    Here's how the Output should look like.

    "Table_A" has 2 columns "User", "File"
    ========
    User File
    ========
    Besi BT
    Besi CL
    Besi CO
    Besi GA
    Besi HA
    Hippo GA
    Hippo HA
    Hippo KA
    Hippo LA

    "Table_B" has 1 column "File"
    ===
    File
    ===
    BT
    CL
    CO
    GA
    HA
    KA
    LA

    Output (should display the non-matching fileName entries in TableA, TableB)
    ========
    User File
    ========
    Besi KA
    Besi LA
    Besi LO
    Hippo BT
    Hippo CL
    Hippo CO
    Hippo LO

    Request you to kindly help mewith the query for VBA.

    Thanks in advance for the timley help.

  2. #2
    Join Date
    May 2016
    Posts
    89
    Provided Answers: 4
    Hi,

    I suggest this solution:

    First query : Select all possible combinations for each user
    Code:
    SELECT DISTINCT TableA.User, TableB.File
    FROM TableA, TableB
    Second, select which combinations are missing in table A
    Code:
    SELECT qryBesi.User, qryBesi.File
    FROM TableA RIGHT JOIN qryBesi ON (TableA.File = qryBesi.File) AND (TableA.User = qryBesi.User)
    WHERE (((TableA.User) Is Null) AND ((TableA.File) Is Null));
    If you merge the both queries

    Code:
    SELECT tblRef.User, tblRef.File
    FROM TableA RIGHT JOIN (SELECT DISTINCT TableA.User, TableB.File
    FROM TableA, TableB) as tblRef
     ON (TableA.File = tblRef.File) AND (TableA.User = tblRef.User)
    WHERE (((TableA.User) Is Null) AND ((TableA.File) Is Null));

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    If you dont understand the above

    Create a Query join the 2 Table them right click on the black line that join the tables and change the Join option
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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