Delete Query Not Working (Access 2003)

[dawnd3]

Hi there, what am I doing wrong? I am trying to delete the records from one table that don't match up with the records from another table (based on NationalID field)

Here is the SQL:

DELETE MemberInfo.*, MemberInfo.NationalID, tblFPAData.ID
FROM tblFPAData RIGHT JOIN MemberInfo ON tblFPAData.ID = MemberInfo.NationalID
WHERE (((MemberInfo.NationalID) Is Not Null) AND ((tblFPAData.ID) Is Null))
WITH OWNERACCESS OPTION;


Thanks!!!

Dawn

http://www.calliopeconsulting.net

 

[PHV]

So you want to delete the rows from MemberInfo that don't match tblFPAData ?
You may either try this:
DELETE MemberInfo.* FROM MemberInfo LEFT JOIN tblFPAData ON MemberInfo.NationalID = tblFPAData.ID
WHERE MemberInfo.NationalID Is Not Null AND tblFPAData.ID Is Null
Or this:
DELETE FROM MemberInfo
WHERE NationalID Is Not Null
AND NationalID Not In (SELECT ID FROM tblFPAData)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886