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
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