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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
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
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 
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.
 
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.
 
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.
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top