Delete Query

[m stroup]

LM_Billets
slot(PK)
dept
LM_People
slot(FK)

I have the following query set up. I want to be asked for a dept and then
delete all records in the LM_People table who are assigned to that dept.
(If I can delet the linked record from LM_Billet at the same time, it would
be sweet.)

DELETE LM_People.*, LM_Billets.*, LM_Billets.Department
FROM LM_Billets INNER JOIN LM_People ON LM_Billets.Slot = LM_People.Slot
WHERE (((LM_Billets.Department)=[Enter Department]));

I get the error message: Could not delete from specified tables.
I have tried removing LM_Billets from query fields as well. Same message.
Any thoughts?

Any suggestions

 

[John Spencer]

The following MIGHT work for you

DELETE DISTINCTROW LM_People.*
FROM LM_Billets INNER JOIN LM_People ON LM_Billets.Slot = LM_People.Slot
WHERE (((LM_Billets.Department)=[Enter Department]));

Otherwise, you can use something like the following
DELETE
FROM LM_People
WHERE Slot IN
(SELECT P.Slot
FROM LM_Billets INNER JOIN LM_People as P
ON LM_Billets.Slot = P.Slot
WHERE LM_Billets.Department=[Enter Department])

And you can only delete from one table at a time, so you need a second query
to delete the records in the LM_Billets table.

Alternative is if you have set up a relationship with cascade delete set to
true then if you delete a billet record the associated people record could be
automagically deleted. This could be a good thing or it could be a bad thing.
If you wanted to delete a billet and reassign the person, it would not be
a good thing if the person got deleted when the billet was deleted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[m stroup]

Thanks John. I figured I would have to have two queries. But I hoped!
I appreciate your help!
--
Teach me to fish! Thanks for the help.
Pax, M

The following MIGHT work for you

DELETE DISTINCTROW LM_People.*
FROM LM_Billets INNER JOIN LM_People ON LM_Billets.Slot = LM_People.Slot
WHERE (((LM_Billets.Department)=[Enter Department]));

Otherwise, you can use something like the following
DELETE
FROM LM_People
WHERE Slot IN
(SELECT P.Slot
FROM LM_Billets INNER JOIN LM_People as P
ON LM_Billets.Slot = P.Slot
WHERE LM_Billets.Department=[Enter Department])

And you can only delete from one table at a time, so you need a second query
to delete the records in the LM_Billets table.

Alternative is if you have set up a relationship with cascade delete set to
true then if you delete a billet record the associated people record could be
automagically deleted. This could be a good thing or it could be a bad thing.
If you wanted to delete a billet and reassign the person, it would not be
a good thing if the person got deleted when the billet was deleted.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

LM_Billets
slot(PK)
dept
LM_People
slot(FK)

I have the following query set up. I want to be asked for a dept and then
delete all records in the LM_People table who are assigned to that dept.
(If I can delet the linked record from LM_Billet at the same time, it would
be sweet.)

DELETE LM_People.*, LM_Billets.*, LM_Billets.Department
FROM LM_Billets INNER JOIN LM_People ON LM_Billets.Slot = LM_People.Slot
WHERE (((LM_Billets.Department)=[Enter Department]));

I get the error message: Could not delete from specified tables.
I have tried removing LM_Billets from query fields as well. Same message.
Any thoughts?

Any suggestions