I have two tables, tblMain and tblWorked. I want to delete records from tblMain where tblMain.loan_number = tblWorked.loan_number. When I start a new query by selecting new query, design view. I brought up the tblMain and tblWorked then joined the two tables by loan_number. In the first column, Field has tblMain.*, Table has tblMain, Delete has From. The second column, Field has loan_number, Table has tblMain, Delete has Where, Criteria has tblWorked.loan_number. When I select the view button, I see the records I want to delete. But when I run the query, it states "Cannot delete from specified tables. I've also tried to use the following SQL statement:

delete tblMain.*
from tblMain
where tblMain.loan_number = tblWorked.loan_number

This returns a box asking me to enter the parameter value. What am I doing wrong???

What is the relationship between tblMain and tblWorked? Is Referential Integrity with cascade deletes and cascade updates enforced? If so, try deleting from the child table (suspect it's tblWorked) FIRST, then the main table.

HTH,

Loralee

currently it's just a one-to-one relationship. I want to keep the records in the tblWorked table and delete the matching records in the tblMain table (since they have not been worked). Then I want to append the worked records from tblWorked to tblMain. And the reason for this is because of multiple users working from their individual laptops without a network connection.

You need to append the records from tblWorked to tblMain FIRST,

Then delete the records from tblWorked,

Then delete the records from tblMain.

You can do this in 3 seperate queries. The first an Append Query, and the other two Delete Queries.

DoCmd.SetWarnings False
DoCmd.OpenQuery "AppendQuery"
DoCmd.OpenQuery "DeletetblWorked"
DoCmd.OpenQuery "DeletetblMain"
DoCmd.SetWarnings True

Did that work?

mmcdonal

Yes, that got it. Thanks.