Drop all the rows which a field value matches in both data-sets.

[mgarret]

Hi all



I am new to MS access so, any assistance in greatly appreciated. I have two queries. I need to produce a third query which deletes all the rows where Child Name is found in both. So, if [Child Not Seen_Pull].[Child_Name] matches in [Child Seen_Pull].[Child_Name] in the new query the rows which meet the criterion (Matching Names) are removed. Not sure how to write for that in the access queries. Thanks!
**Query 1 [Child Not Seen_Pull] **
SELECT [Child Not Seen_Clean].ID, [Child Not Seen_Clean].[Case ID], [Child Not Seen_Clean].Child_Name, [Child Not Seen_Clean].[Worker Site / Unit], [Child Not Seen_Clean].[Worker Name], [Child Not Seen_Clean].[Worker Role], [Child Not Seen_Clean].[Contact Date], [Child Not Seen_Clean].[Contact Method], [Child Not Seen_Clean].[Contact Result], [Child Not Seen_Clean].Focus, [Child Not Seen_Clean].Participant FROM [Child Not Seen_Clean] WHERE ((([Child Not Seen_Clean].[Contact Method])<>"Face To Face") AND (([Child Not Seen_Clean].Participant)<>"Yes")) OR ((([Child Not Seen_Clean].[Contact Method]) Is Null)) OR ((([Child Not Seen_Clean].[Contact Method])="Face to Face") AND (([Child Not Seen_Clean].[Contact Result])="Attempted")) OR ((([Child Not Seen_Clean].[Contact Method])="Face to Face") AND (([Child Not Seen_Clean].[Contact Result])="Contacted") AND (([Child Not Seen_Clean].Focus)="Yes") AND (([Child Not Seen_Clean].Participant)="No")) OR ((([Child Not Seen_Clean].[Contact Method])="Phone") AND (([Child Not Seen_Clean].[Contact Result])="Contacted") AND (([Child Not Seen_Clean].Focus)="Yes")) OR ((([Child Not Seen_Clean].[Contact Method])="Fax") AND (([Child Not Seen_Clean].[Contact Result])="Contacted") AND (([Child Not Seen_Clean].Focus)="Yes")) OR ((([Child Not Seen_Clean].[Contact Method])="Phone") AND (([Child Not Seen_Clean].[Contact Result])="Attempted") AND (([Child Not Seen_Clean].Focus)="Yes")) OR ((([Child Not Seen_Clean].[Contact Method])="Mail") AND (([Child Not Seen_Clean].[Contact Result])="Attempted")) ORDER BY [Child Not Seen].[ID];
Query 2
SELECT [Child Not Seen_Clean].[ID], [Child Not Seen_Clean].[Case ID], [Child Not Seen_Clean].Child_Name, [Child Not Seen_Clean].[Worker Site / Unit], [Child Not Seen_Clean].[Worker Name], [Child Not Seen_Clean].[Worker Role], [Child Not Seen_Clean].[Contact Date], [Child Not Seen_Clean].[Contact Method], [Child Not Seen_Clean].[Contact Result], [Child Not Seen_Clean].Focus, [Child Not Seen_Clean].Participant FROM [Child Not Seen_Clean] WHERE ((([Child Not Seen_Clean].[Contact Method])="Face To Face") AND (([Child Not Seen_Clean].[Contact Result])<>"Attempted") AND (([Child Not Seen_Clean].Participant)="Yes")) ORDER BY [Child Not Seen].[ID];

[June7]

What do you mean by 'delete'? You just don't won't the records to show or you actually want to delete records from tables?

Try:

SELECT [Child Not Seen_Pull].* FROM [Child Not Seen_Pull] WHERE NOT [Child_Name] IN (SELECT [Child_Name] FROM [Child Seen_Pull]);