delete records in one table that occur in another table

Joined
Oct 31, 2012
Messages
2
Reaction score
0
hi folks, i know this has been posted before but i'm not getting any joy from the responses. I'm still a gumby writing code, which is probably what the root of the problem is. I'm using Access 2007

I have two tables: 1. REGISTRATIONS and 2.RNs_NOT_IN_STUDY_AREA.RN

I need to delete all records in the REGISTRATIONS table that occur in the RNs_NOT_IN_STUDY_AREA.RN. The bores not in study area table contains one field only (RN). Essentially what I want the DELETE query to do is delete any records in the REGISTRATIONS table where the record has an RN that is listed in the RNs_NOT_IN_STUDY_AREA.RN (RN is basically an ID).

I've seen this response in another thread:

The query is:
DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField;

Which I translate to:
DELETE REGISTRATIONS.*
FROM REGISTRATIONS INNER JOIN RNs_NOT_IN_STUDY_AREA ON REGISTRATIONS.RN = RNs_NOT_IN_STUDY_AREA.RN;

result is: could not delete from specified tables

I also saw this solution from another thread:

DELETE tblCust.custid FROM tblCust
WHERE ((tblCust.custid) In (SELECT tblCust.custid
FROM tblCust LEFT JOIN tblOrders ON tblCust.custid = tblOrders.custid
WHERE (tblOrders.custid) Is Null));

which i translated to:

DELETE REGISTRATIONS.* FROM REGISTRATIONS
WHERE ((REGISTRATIONS.RN) In (SELECT REGISTRATIONS.RN
FROM REGISTRATIONS LEFT JOIN RNs_NOT_IN_STUDY_AREA ON REGISTRATIONS.RN = RNs_NOT_IN_STUDY_AREA.RN
WHERE (RNs_NOT_IN_STUDY_AREA.RN) Is Null));

result is: i can run the query, but it seems to delete everything (the Datasheet view has no records in it). The result should be a table with records for RNs that aren't in the RNs_NOT_IN_Study_Area
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top