Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to delete all records where edits were not applied. i tried this:

    Code:
    DELETE [Table A].*, [Table B].[Form Dln]
    FROM [Table A] LEFT JOIN qryEditRUGItems ON [Table A.FORM_DLN = qryEditRUGItems.[Form Dln]
    WHERE (((qryEditRUGItems.[Form Dln]) Is Null));
    And access just won't do it: Cannot delete from table. this occurs when all forms are closed, so I suspect there's some issue with the syntax. how do you do a batch delete + find unmatched type query?

    I tried a WHERE EXISTS but am not very familiar with this so the fact that it didn't work is not too surprising....I'll keep working on it, just thought i'd throw it out to the lounge for your viewing pleasure...

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Where does Table B come into this?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Table B is in the same Access file. also, i don't have to reference a query in the join as the edited records are actually in another table. so you can also read the code as

    Code:
    DELETE [Table A].*, [Table B].[Form Dln]
    FROM [Table A] LEFT JOIN [Table B] ON [Table A].FORM_DLN = [Table B].[Form Dln]
    WHERE ((([Table B].[Form Dln]) Is Null));
    which also does not work

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    DELETE * FROM [Table A] WHERE FORM_DLN Not In (SELECT [Form Dln] FROM [Table B])

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! that seems to have done 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
  •