can not delete from specified tables 2

[jadams0173]

I've read several posts about this and I've tried to fix it using a sub-query in the where statement but I'm struggling and desperate for some help. Here is the sql

DELETE TBLScheduled.*, Schedule_j.SCHEDULED, TBLScheduled.RELEASED
FROM Schedule_j INNER JOIN TBLScheduled ON Schedule_j.MO_NUMBER = TBLScheduled.MO
WHERE (((Schedule_j.SCHEDULED)=0) AND ((TBLScheduled.RELEASED)=0))

Thanks in advance.

 

[PHV]

You may delete rows from only one table.
I guess you wanted something like this:
DELETE T.*
FROM TBLScheduled AS T INNER JOIN Schedule_j AS S ON T.MO = S.MO_NUMBER
WHERE S.SCHEDULED = 0 AND T.RELEASED = 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV,

Thanks for you reply. I want to delete the returned records from tblScheduled. I pasted your sql in access and got the same error.

DELETE T.*, S.SCHEDULED, T.RELEASED
FROM TBLScheduled AS T INNER JOIN Schedule_j AS S ON T.MO = S.MO_NUMBER
WHERE (((S.SCHEDULED)=0) AND ((T.RELEASED)=0))


Can you please offer anymore suggestions.

 

[PHV]

I pasted your sql in access
I don't think so, you last posted SQL code is not mine ...
Anyway:
DELETE T.*[!], S.SCHEDULED, T.RELEASED[/!]
FROM TBLScheduled AS T INNER JOIN Schedule_j AS S ON T.MO = S.MO_NUMBER
WHERE (((S.SCHEDULED)=0) AND ((T.RELEASED)=0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Sorry for the miss post. I still get the same error. When I change the query to a SELECT query I see the records to delete by the recordset is not updateable. Could that be a problem?

Any more suggestion phv or anyone?

 

[PHV]

And this ?
DELETE FROM TBLScheduled
WHERE RELEASED = 0 AND MO In (SELECT MO_NUMBER FROM Schedule_j WHERE SCHEDULED=0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Thanks again PHV, however now I get an error stating query must have at least one destination field.

 

[PHV]

query must have at least one destination field
???
Which EXACT SQL code raised this error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV,

I'm sorry for the confusion, but you SQL does work. I was attempting to preview the results of the delete query (which would make it a select query) and Access didn't know what to do. When executing the query it runs without error.

Sorry for the confusion and thank you very much for the help.