Error: "Operation must use an updatable query."

[denveromlp]

I've been getting the following error in a delete query.

This is the SQL:

DELETE tbl_missingroadnummaster.*
FROM qry_missingfleets INNER JOIN (tbl_missingroadnummaster INNER JOIN tbl_locodata ON tbl_missingroadnummaster.ROAD_NUMBER = tbl_locodata.ROAD_NUMBER) ON qry_missingfleets.FLEET_NAME = tbl_locodata.FLEET_NAME;


Ever run into this before?
Thanks!

[PianoMan64]

@denveromlp
Hello denveromlp,

Since you're trying to link acrossed multiple table, this is a feature that isn't allowed at all. You'll have to delete each table with a set of conditions so you don't delete every record.

But once you join a table, it makes it READ ONLY.

Sorry I couldn't be of more help. Without any more detail that you have, it's kind of hard to guess.

Hope that helps,

Joe P.

[OldBirdman]

If these tables were linked using Tools->Relationships... and "Enforce Referential Integrity were checked, you could then click "Cascade Delete Related Records".

Deleting the table on the "One" side of a "One-to-Many" relationship will also delete the records on the "Many' side.

OldBirdman

[denveromlp]

OK, so joining tables disallows you to complete a delete query.

I'm trying to delete records contained in the table "tbl_missingfleets" from the table "tbl_missingroadnummaster". Now I'm trying the following SQL, but it wants me to "specify the table containing the reconds I want to delete." How do I do that?

DELETE tbl_missingroadnummaster.ROAD_NUMBER, tbl_missingroadnummaster.FLEET_NAME, tbl_missingroadnummaster.DOWNLOAD_DATE, tbl_missingroadnummaster.MOTOR_MWHRS
FROM tbl_missingroadnummaster, tbl_missingfleets
WHERE (((tbl_missingroadnummaster.ROAD_NUMBER)=[tbl_missingfleets]![FLEET_NAME]) AND ((tbl_missingroadnummaster.FLEET_NAME)=[tbl_missingfleets]![FLEET_NAME]) AND ((tbl_missingroadnummaster.DOWNLOAD_DATE)=[tbl_missingfleets]![FLEET_NAME]) AND ((tbl_missingroadnummaster.MOTOR_MWHRS)=[tbl_missingfleets]![FLEET_NAME]));

...one way I've found to accomplish this is to run an update query for tbl_missingroadnummaster and set all the records common to both tables to null, then create a delete query to delete all null records from tbl_missingroadnummaster, just curious if there is a way to do this in one query

[ChipR]

Take a look at the examples here:
SQL: EXISTS Condition

[denveromlp]

EXIST function worked great, thanks!