Results 1 to 4 of 4
  1. #1
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28

    Find all occurrences of a list of item#s in a table

    I have a table with just a column listing 5606 item#s. Each of them occur just once. I want to run a query to find all of the occurrences of those 5606 in another table where those item#s occur multiple times each, so I can then run a delete query to delete all of those occurrences. I can't seem to figure out how to run an unmatched-style query to do this.

    So it's basically:

    TableA - TableB -
    ItemNum joins ItemNum

    Records in Table A are something like:

    ItemNum
    100101
    102
    103

    Records in Table B are something like:

    ItemNum
    100
    100
    100
    100101


    101
    101
    101
    102
    102
    102
    103
    103
    103
    103
    103

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Are you trying to delete records in table B if the ItemNum does not exist in table A? An unmatched query will do this - use the wizard to create the query, with all ItemNum's in table B missing in table A. Once you have that query showing the records you want to delete, change it into a delete query.

  3. #3
    Tambe257 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Mar 2017
    Posts
    28
    No I'm trying to delete the records in table B if it DOES exist in table A. Basically I want to say, "Look at all of the item#s in table A, and every time you see one of those in table B, then delete the record."

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Use a subquery, such as:
    DELETE [TableB].*
    FROM [TableB]
    WHERE (SELECT TableA.Field1 FROM TableA WHERE TableA.Field1=[TableB].Field1);

    From http://allenbrowne.com/subquery-01.html

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

Similar Threads

  1. Add item to value list in list box - error
    By slochhaas in forum Forms
    Replies: 7
    Last Post: 01-31-2017, 03:22 PM
  2. Item Master List | Products Table
    By namu23 in forum Access
    Replies: 4
    Last Post: 10-02-2013, 10:33 PM
  3. Replies: 2
    Last Post: 02-19-2013, 04:02 PM
  4. Replies: 2
    Last Post: 04-05-2012, 08:39 PM
  5. Replies: 0
    Last Post: 08-26-2009, 11:51 AM

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
  •  
Other Forums: Microsoft Office Forums