Results 1 to 3 of 3
  1. #1
    udigold1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2010
    Posts
    29

    Getting items that no on another table

    I have two tables that share the same columns - BankID, ISIN, Desc.
    Table1 is raw data that I get from and outside sources and table2 (a smaller one in terms of rows) manages duplicate values that might appear in the table1.


    I want to find only BANKID that appears in Table1 and doesn't appear in Table2 but has another BankID with the same ISIN on table2.

    I tried the following query, but it gives me everything that is not on table2, not just the one that has the same ISIN.
    Code:
    SELECT distinct tb1_isin, tb1_BankID
     FROM table1
    WHERE not EXISTS (
        SELECT top 1 null
        FROM  table2
        WHERE   table1.tb1_ISIN = table2.tb2_isin
    )
    What am I doing wrong?
    Thanks,

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Perhaps one way is to do the following:

    1. Create an Unmatched Query from Table1 to Table2, returning all the records with BANKID found in Table1 but not in Table2.
    2. Create another query that matches Table1 and Table2 on ISIN.
    3. Create a third query with links your first two queries on BANKID.

    If this does not do what you want, please post some small data example of each table and the expected results that you want based on those two samples.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Try
    Code:
    SELECT t1.*
    FROM Table1 t1
    WHERE t1.BankID NOT IN (SELECT t21.BankID FROM Table2 t21) AND t1.ISIN IN (SELECT t22.ISIN FROM Table2 t22 WHERE t22.BankID <> t1.BankID);

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

Similar Threads

  1. Replies: 2
    Last Post: 02-13-2018, 07:06 PM
  2. List of items within a table row
    By hoodoo in forum Database Design
    Replies: 2
    Last Post: 09-21-2015, 10:32 AM
  3. Query to exclude items from another table
    By BrockWade in forum Queries
    Replies: 5
    Last Post: 02-20-2014, 03:56 PM
  4. Replies: 1
    Last Post: 06-08-2011, 04:43 AM
  5. Add Items from Form to table
    By swalsh84 in forum Forms
    Replies: 6
    Last Post: 06-14-2010, 06:53 AM

Posting Permissions

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