Delete Query "Could not delete from specified table"

[Cheshire101]

DELETE DISTINCTROW tblEmployeeReq.*, qryOperFunctReq.OperFunction
FROM tblEmployeeReq LEFT JOIN qryOperFunctReq ON (tblEmployeeReq.ReqNo = qryOperFunctReq.ReqNo) AND (tblEmployeeReq.EID = qryOperFunctReq.EID)
WHERE (((qryOperFunctReq.OperFunction) Is Null));

Ok- when I run the above query in select mode in generates the correct information.

If I remove one of the joins then it delete the records, but not the correct ones.

Solutions Tried:
1 - The Unique Record is turned to "Yes"
2 - Yes I have administrative access to the database. Also, it is not networked yet.

Any help would be greatly appreciated

[Cheshire101]

Also, there are no relationship problems. I deleted all my relationships and that did not solve my problem.

[Cheshire101]

Also, I tried turning my delet query above into a select query and then creating another query to do actual deleting. That resulted in the same error.

[ajetrumpet]

you deleted all the relationships and you're still trying to run a delete query with a join statement??

can you upload the file?

[Cheshire101]

I have attached the query portion of the database.

Thank you in advance for your help

[orange]

What exactly do you want to delete?
You can only delete from one table at a time.

[Cheshire101]

The delete query is only deleting from one table. Like I said in the first post I have a double left join to another query. If I remove one (it does not matter which one) the query will delete records, but of course not the correct records.

[ajetrumpet]

The delete query is only deleting from one table.

I would suspect so, if there are no relationships set up. Aren't relationships required for joins to work correctly!?

But at any rate, you need to change things. Your query joins two tables on statements. You'd be better off joining these two tables with a direct relationship, enable the cascading delete feature, and then simply run a delete statement on your parent table's distinct records. The rest of the child records get shed via the cascading option you turned on.

Are you also aware of using DISTINCTROW?? I don't know why you're using it. Usually, you don't see it used with something like this, which appears to be a situation where you need to cascade your deletes. Also, DISTINCTROW has a few quirks with it that are sometimes undetectable, but on the surface appear to be program bugs when they happen.

What exactly do you want to delete?
You can only delete from one table at a time.

Careful, sir. Cascading deletes can override that statement in a general sense.

[Cheshire101]

I cannot use cascading because the records in the table that I am deleting from are not directly related to the table I am using to update. (That is why I am using the initial query to get the data that should be in the table). Technically I could delete all records from the table and then add the records I want but I lose a date field of when the record was originally entered.

[ajetrumpet]

I apologize Ches, this thread is confusing me. I'm not sure how to help at this point.

Perhaps orange can. Again, i apologize, but I'm not able to understand what's going on. good luck with it!

[Cheshire101]

Ok I figured a work around the problem. I first run a query that updates a certain field in the table I am trying to delete from with the 'Delete' and then I run a secondary query to delete any record with that has 'Delete' as a requirement.

[orange]

Are these the records you feel should be deleted? I'm just identifying these fields so you can compare with your records.

SELECT tblEmployeeReq.EID, tblEmployeeReq.ReqNo, tblEmployeeReq.IssueDate, tblEmployeeJob.OperFunction
FROM tblEmployeeReq LEFT JOIN tblEmployeeJob ON tblEmployeeReq.EID = tblEmployeeJob.EID
WHERE (((tblEmployeeJob.EID) Is Null));