Results 1 to 8 of 8
  1. #1
    blackburn2413 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    4

    Question Delete row with matching Company_ID

    Hey everyone, got an interesting one and just looking to be pointed in the right direction:



    I have 2 tables in my database: "current clients" and "clients that canceled". Each company has a unique company ID that is used in both tables. What my goal is is to write something that will take all of the company ID's from clients that canceled and then search the "current clients" table and delete all companies with matching ID's.

    I am coming from a world of mySQL and php, but unfortunately my company now requires Sharepoint and Office products to be used exclusively. I was figuring I could run a loop that takes each canceled company ID line by line and checks the other table to see if it exists. If it finds a result, then I would want it to delete that entire row of data.

    That's the goal. Anyone have a direction they can point me in? Is this something best suited for a customized macro?

    Thanks in advance!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Be cautious of DELETE query. There's NO simple UNDO.

    I recommend you Backup your database first.
    Create a SELECT query to make sure you are dealing with the proper records.
    Once you are sure you have the correct records.\, convert your SELECT query to a DELETE query.

    Backup your database first.

    There are example DELETE queries showing syntax at http://www.techonthenet.com/sql/delete.php

    Don't forget to Backup your database.

  3. #3
    blackburn2413 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    4
    Thanks for that. I am running queries now that are working out and returning the results that I need, so step one complete I guess The next issue would be, how can I change the criteria that I am using for my query? For instance: Table2 we'll call it has a list of unique ID numbers in a field, I can manually setup a query with an ID number and delete any matching records from Table1. I can get that far at this point. What I am wondering is if there is a way to almost run it in a loop so that it takes every ID number stored in Table2 and deletes all matching records in Table1. So almost a dynamic query in a sense that the criteria can change based on the data stored in Table2.

    Does that make sense?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Step 1 is BACKUP your database.

    Select Table1.* from Table1 Inner join Table2 on
    Table1.id = Table2.ID

    This type of query should give you the records in Table1 that have matching numbers(IDs) in table2.
    Is that what you are doing?

  5. #5
    blackburn2413 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    4
    That was not the initial way I planned on doing it, but I have entered that as you mentioned and see that it is working. My apologies on behind the curve, I am so used to using PHP and having a loop counter and subbing in each value from the table with a variable like $row[$i] or something like that. Just a little bit of new Syntax work I guess.

    So theoretically, I can just alter this select statement to make sure it is getting all the results I want and then change it over to a delete statement to get my requested results?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Yes, but do the backup because there is no simple recovery if there is an error.
    Be very careful with the delete query syntax...

    I would look at something like this set up, but check the link I gave you before for syntax and BACKUP first.

    DELETE FROM Table1 WHERE ID
    IN ( select ID from Table1 inner join table2 on
    Table1.ID = Table2.ID)

  7. #7
    blackburn2413 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    4
    Thank you, I really appreciate it- this data I am working with is easily exportable (from our isolated Reporting server) so I am just messing around with it for now. I actually just got this working which is great to see. I really appreciate the help today!

    I am going to keep messing with some of the options and see what else I can have this do.

    Thanks again!

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849

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

Similar Threads

  1. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  2. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  3. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 02:30 AM
  4. Replies: 1
    Last Post: 08-11-2011, 11:33 AM
  5. String Matching
    By tuna in forum Programming
    Replies: 1
    Last Post: 05-16-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