Results 1 to 4 of 4
  1. #1
    FuelingAround is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    2

    Delete query with criteria from another table

    I have the following SQL statement which under a select statement works great. The issue is I just want to use the status field as a criteria on what to delete from the sp_excpt_cust table. Can someone point me in the right direction here?

    DELETE ssfactor_sp_excpt_cust.ce_cust, ssfactor_sp_excpt_cust.ce_prodlnk, ssfactor_sp_excpt_cust.ce_lvl, ssfactor_ar_customer.cust_status


    FROM ssfactor_sp_excpt_cust INNER JOIN ssfactor_ar_customer ON ssfactor_sp_excpt_cust.ce_cust = ssfactor_ar_customer.cust_customer
    WHERE (((ssfactor_ar_customer.cust_status)="CL"));

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    Deleting records should be a rare occurrence. Why do you want to delete?

    No need to specify fields in a DELETE action as the entire record is removed.

    DELETE FROM ssfactor_sp_excpt_cust WHERE ce_cust IN (SELECT ce_cust FROM ssfactor_ar_customer WHERE status="CL")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    FuelingAround is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2015
    Posts
    2
    sp table holds customer rebate amounts. I want to delete all records for customers who are no longer active so we have a smaller piece of data to manage. It doesn't effect any historical data as the system refers to the table when it makes a sale to that customer.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    The records could be filtered.

    You don't care about history of rebates?

    Did you try the suggested query?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-07-2015, 11:33 AM
  2. Delete Query - Use Another Table As Criteria
    By Madmartigan in forum Access
    Replies: 4
    Last Post: 07-12-2013, 01:15 PM
  3. Replies: 5
    Last Post: 01-24-2012, 06:19 PM
  4. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  5. Replies: 0
    Last Post: 04-08-2010, 12:22 PM

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