Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: How to properly filter table

    I have Table A which has records inside, identified by a non-unique ID
    I have Table B which has records inside, identified by a non-unique ID

    What I would like to do is remove the records from Table A which has an ID that exists in Table B.
    How can I do this? Thank you for any help in advance.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You need a two-step approach:

    1) A simple query on Table A:
    Code:
    SELECT ID FROM Table A GROUP BY ID
    Save this query as qryIDSub, for example. Of course, ID and Table A in the SQL statement should be substituted with the correct object names.

    2) Another query:
    Code:
    DELETE * FROM Table B INNER JOIN qryIDSub ON Table B.ID = qryIDSub.ID
    That should work.

    The point is, you have to isolate the unique IDs within Table A before you can run the delete query.

    BTW, Access will probably not even delete as requested, and may give you an error message "must use updateable query". If that's the case run the first query (qryIDSub) as a make-table query and make a new name, and change the second query (the delete query) to use the new temp table in the INNER JOIN.

    Good luck,

    Sam

Posting Permissions

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