How to make this delete query?

[cdoyle]

OK,
I know what records I need to delete, just not sure how to do this.

I have 2 tables.
My main table, and a child table that hold some additional info for that record.

What I've been asked to do is, delete all records where in the child table the records meet a certain criteria.

So first I created a select query, added my main table, and then my child table.

Added my criteria to filter out and found the records they want removed.

So I thought I could just convert it to a delete query and be done. Nope because of the relationship between the tables it would not let me do it this way.

So the select query shows me all the ID#s that need to be removed (194k worth of them). How do I use the data from the select query, to create a delete query to remove all the files from the maintable (which should delete the data from the child table because of the relationship?)?

Here is the select query, that found the records that need to be deleted

SELECT tbl_Edit_Type_IRR.Information, tbl_Edit_Type_IRR.Issue, tbl_Edit_Type_IRR.Review, tbl_Edit_Type_IRR.Main_ID, T_tbl_main.Main_ID
FROM T_tbl_main INNER JOIN tbl_Edit_Type_IRR ON T_tbl_main.Main_ID = tbl_Edit_Type_IRR.Main_ID
WHERE (((tbl_Edit_Type_IRR.Information) Is Null) AND ((tbl_Edit_Type_IRR.Issue) Is Null) AND ((tbl_Edit_Type_IRR.Review) Is Null));

 

[CraigDolphin]

You could try

DELETE DISTINCTROW tbl_Edit_Type_IRR.Information....etc

if that doesn't work one workaround would be to convert your select query into a maketable query to create a temp table that stores the pk of the records you want deleted from the table.

Then join the temptable to your main table in a delete query and delete all the records where the pk values are common to both tables.

 

[cdoyle]

You could try

DELETE DISTINCTROW tbl_Edit_Type_IRR.Information....etc

if that doesn't work one workaround would be to convert your select query into a maketable query to create a temp table that stores the pk of the records you want deleted from the table.

Then join the temptable to your main table in a delete query and delete all the records where the pk values are common to both tables.

I got it to work by doing option #2. I didn't think of after getting all the results from the new query, just selcting all delete (lol) I was making it too complicated.

 

[cdoyle]

GRR, I guess it didn't work.

I should have checked closer, Once I find the PK's that I need to remove I create the temp table that stores it.

I then created a new query, add my main table and my temp table it draws the line between both Main_ID's.

I enter all my fields from my maintable *

I then just selected all and delete.

but instead of deleting from my main table, it deletes them from the temp table?
Am I doing this right?

I tried also using a delete query from this, and I get a 'could not delete from specified table.

 

[CraigDolphin]

When you're adding the pk field to the query grid, make sure you're choosing the one in your main table, not your temp table.

You might also need to use the

'Delete Distinctrow...' syntax if you're doing this via a delete query. I agree with you that this is a PITA issue to deal with.

 

[cdoyle]

Ya I was making sure I was selecting my main table, but no matter how many times I did it, it would delete from my temp table.

but I tried the delete dinstinctrow, and I think it worked this time!!

I wrote down my totals of all the tables queries before I ran them, and then subtracted after and the numbers match to what I see. So I think it worked

 

[CraigDolphin]

Good Because I'm not sure I have anything else up my sleeve if that didn't work!