Results 1 to 2 of 2
  1. #1
    mgarret is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    8

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


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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    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]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-19-2013, 08:15 AM
  2. Replies: 1
    Last Post: 11-08-2012, 02:37 PM
  3. Selecting Unique SETS of rows in Access Query
    By dalessandroh in forum Queries
    Replies: 0
    Last Post: 11-09-2010, 10:42 PM
  4. Replies: 2
    Last Post: 02-13-2010, 01:54 PM
  5. Replies: 1
    Last Post: 05-29-2008, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums