none
Delete Records from Unmatched Query RRS feed

  • Question

  • I have an unmatched query which finds records in my Contact table that are not present in my newly imported TempContact table. I would like to archive and delete them. I have a query that will append them to the Archive table, but how can I delete them after archiving.


    MS - Teach me to fish

    Tuesday, October 17, 2017 6:50 PM

Answers

  • Hi,

    How about using a DELETE query joining the archive and the import (Contact) table? Make sure you delete from the Contact table. For example:

    DELETE FROM Contact INNER JOIN Archive ON Archive.FieldName=Contact.FieldName

    Hope it helps...

    • Marked as answer by lismeta Tuesday, October 17, 2017 7:33 PM
    • Unmarked as answer by lismeta Tuesday, October 17, 2017 7:35 PM
    • Marked as answer by lismeta Tuesday, October 17, 2017 7:35 PM
    Tuesday, October 17, 2017 7:03 PM

All replies

  • Hi,

    How about using a DELETE query joining the archive and the import (Contact) table? Make sure you delete from the Contact table. For example:

    DELETE FROM Contact INNER JOIN Archive ON Archive.FieldName=Contact.FieldName

    Hope it helps...

    • Marked as answer by lismeta Tuesday, October 17, 2017 7:33 PM
    • Unmarked as answer by lismeta Tuesday, October 17, 2017 7:35 PM
    • Marked as answer by lismeta Tuesday, October 17, 2017 7:35 PM
    Tuesday, October 17, 2017 7:03 PM
  • Thanks. That worked. I was just trying to minimize the number of queries for the task. Thanks much.


    MS - Teach me to fish

    Tuesday, October 17, 2017 7:36 PM
  • Hi,

    You're welcome. Unfortunately, I am not sure if there is one query that can do both INSERT records into one table and then DELETE them at the same time. If you don't want the user to execute two queries, you can create a macro to execute them for the user. So, the user simply clicks on one button, and all the queries you need to execute for the task get executed in succession.

    Just my 2 cents...

    Tuesday, October 17, 2017 7:48 PM