1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Delete Query to Delete Parent with no Child Records

Discussion in 'Business Applications' started by mariaa33, May 20, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I am trying to create a delete query to delete all parent records (DGRP) that does not have any child records (CLLI) but I get the error "Could not delete from specific tables".

    DELETE DGRP.*
    FROM DGRP LEFT JOIN CLLI ON DGRP.DGRP = CLLI.DGRP
    WHERE (((CLLI.CLLI) Is Null));

    Can this be accomplished?
     
  2. Jubbaloo

    Jubbaloo

    Joined:
    May 11, 2009
    Messages:
    156
    Unless your hung up on doing this using SQL - I would be tempted to do this using Access' built in query tools.

    Then you can step by step build the query to select the right records and then change it to a delete query.

    I'll post an example in a couple of mins
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,827
    You should be able to do it, you may have to split this in to 2 queries, find all the adult records with CLLI.CLLI that are null and then create a Delete Query based on those records.

    If you and Jubbaloo can't get it to work, I can write you some VBA to do it.
     
  4. Jubbaloo

    Jubbaloo

    Joined:
    May 11, 2009
    Messages:
    156
    It might not be as simple as i first thought - I have done this exact query before - I am just hunting for the DB - might be a while though. If someone else can help hop in.
     
  5. Jubbaloo

    Jubbaloo

    Joined:
    May 11, 2009
    Messages:
    156
    I did basically what OPB suggested, however I get the same error message. I don't think there is a real problem with the code. It just doesn't like something.
     
  6. Jubbaloo

    Jubbaloo

    Joined:
    May 11, 2009
    Messages:
    156
    I knew it was something stupid -

    DELETE DISTINCTROW DGRP.*
    FROM DGRP LEFT JOIN CLLI ON DGRP.DGRP = CLLI.DGRP
    WHERE (((CLLI.CLLI) Is Null));

    That should work for you.
     
  7. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Thank you so much. Works perfectly.
     
  8. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Use the "Find Unmatched Query Wizard" to find the parent records without child or vice versa. Go into it's design view and change it up to a delete query and then you should be fine.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/828625

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice