Deleting One Table from Another

[JimLecocq]

Hi All!

I am relatively new to Access. But over the past few weeks I have developed a large Db. I have searched the Forum under almost every thing I can imagine and have tried to implement some of them.

What I need to do is delete the records in one table from another table. I tried using a delete query but simply get told I "You cannot delete from the specified tables." I have checked almost everywhere I can find to check and the tables are not in read only status and I built it.

I have also tried doing it from a module in VBA with the code:

DoCmd.RunSQL "DELETE tblNewHRoster.* FROM tblRosterwoNHires.*" & _
"WHERE tblRosterwoNHires.[Rep_Name] = tblNewHRoster.[Rep_Name]"

On this I get told there is an "Syntax Error in Query. Incomplete Query Clause."


Can any one help? As you can probably tell I need a lot of it.

 

[Jon K]

Assuming you want to delete the records from table tblRosterwoNHires where the Reps exist in table tblNewHRoster, try this query:-

DELETE *
FROM tblRosterwoNHires
WHERE Rep_Name IN (SELECT Rep_Name FROM tblNewHRoster);


Note: When running a new delete query for the first time, it is advisable to back up your table first.
.

 

[JimLecocq]

Thank Jon

Jon,

Wow and thanks. Worked like a charm!