"Delete Query" Problems

[Devona]

I am trying to run a Delete Query using two tables. Here is what I have so
far:
DELETE DISTINCTROW tblProjects.[Project Phase (Per EPMO)],
tblComments.[Project ID]
FROM tblProjects INNER JOIN tblComments ON tblProjects.ProjectID =
tblComments.[Project ID]
WHERE (((tblProjects.[Project Phase (Per EPMO)])="CMPLT"));

I keep getting asked for the table I want to delete from. What am I doing
wrong. To sum up what I want to delete: I want to delete closed projects in
one table and the comments that go with them from the other table. I already
have an append query to archive these closed projects in another table.
Thank you.

 

[Jerry Whittle]

You really can't delete from two tables at once. Also the DISTINCTROW will
mess things up.

First make a complete backup of your database or even play with a copy of
it. Then you could do it in two steps like so:

DELETE tblComments.*
FROM tblComments
WHERE EXISTS (SELECT "X"
FROM tblProjects
WHERE tblProjects.ProjectID = tblComments.[Project ID]
AND tblProjects.[Project Phase (Per EPMO)]="CMPLT");

DELETE tblProjects.*
FROM tblProjects
WHERE tblProjects.[Project Phase (Per EPMO)])="CMPLT" ;

Actually you could do it with just the second SQL statement IF tblProjects
was the parent in a one to many relationship to tblComments; referiential
integrity was on in the Relationships window between the two; and Cascade
Delete was checked.

Also IMHO archiving records in another table is a bad idea. Better that you
have a Yes/No boolean field for Closed and base all your queries on that.
Actually your [Project Phase (Per EPMO)] field is probably good enough.

 

[DRB]

Thank you, Jerry. I will try your suggestions. Also, the Yes/No boolean is
a good idea. Thanks

You really can't delete from two tables at once. Also the DISTINCTROW will
mess things up.

First make a complete backup of your database or even play with a copy of
it. Then you could do it in two steps like so:

DELETE tblComments.*
FROM tblComments
WHERE EXISTS (SELECT "X"
FROM tblProjects
WHERE tblProjects.ProjectID = tblComments.[Project ID]
AND tblProjects.[Project Phase (Per EPMO)]="CMPLT");

DELETE tblProjects.*
FROM tblProjects
WHERE tblProjects.[Project Phase (Per EPMO)])="CMPLT" ;

Actually you could do it with just the second SQL statement IF tblProjects
was the parent in a one to many relationship to tblComments; referiential
integrity was on in the Relationships window between the two; and Cascade
Delete was checked.

Also IMHO archiving records in another table is a bad idea. Better that you
have a Yes/No boolean field for Closed and base all your queries on that.
Actually your [Project Phase (Per EPMO)] field is probably good enough.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to run a Delete Query using two tables. Here is what I have so
far:
DELETE DISTINCTROW tblProjects.[Project Phase (Per EPMO)],
tblComments.[Project ID]
FROM tblProjects INNER JOIN tblComments ON tblProjects.ProjectID =
tblComments.[Project ID]
WHERE (((tblProjects.[Project Phase (Per EPMO)])="CMPLT"));

I keep getting asked for the table I want to delete from. What am I doing
wrong. To sum up what I want to delete: I want to delete closed projects in
one table and the comments that go with them from the other table. I already
have an append query to archive these closed projects in another table.
Thank you.