Results 1 to 9 of 9
  1. #1
    garrettgivre is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7

    Remove Only Part of Record Using Unmatched Query

    I am completely new with Access and am needing to compare some information in a way I cant with the normal Unmatched Query.

    I have 2 tables with the fields:
    | PHONE1 | PHONE2 | FIRST | LAST |



    What I need to do is have Table 2 check against Table 1 to see if any of the 'PHONE1' records match 'PHONE1' or 'PHONE2,' and if they do have them remove just the information from PHONE1, not the entire record.

    Then I would have to repeat the process to do this with PHONE2. Having it check against 'PHONE1' and 'PHONE2' in Table1.

    If both PHONE1 and PHONE2 have a match, the entire record would be deleted. If not, it would keep whichever did not match.

    I am currently having to do this manually, because if I just run an Unmatched Query it gets rid of the whole record regardless of if PHONE2 is a good number or not.

    If any more information is needed, let me know. Like I said, Im relatively new to working in Access and dont know much.

    All of the fields are 'TEXT' type.

    Access 2003 on Windows XP.

  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,632
    Would have to run an UPDATE query to set the field to null.
    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.

  3. #3
    garrettgivre is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    Would have to run an UPDATE query to set the field to null.
    Im not sure I understand. I am pretty new to Access and do not know how to run an update query to set the field to null. I assume this makes the blank fields (Where PHONE1 from Table2 matches PHONE1/2 from Table1) fill with 'NULL' so I would be able to run the second check? How do I get it to do this?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If Table1 Phone1 is in ANY record of Table2 Phone1 or Table2 Phone 2, you want to remove Table1 Phone1? And the same for Table1 Phone2?

    I expect after those actions, any records in Table1 that result in Null in both fields can simply be deleted.
    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.

  5. #5
    garrettgivre is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    If Table1 Phone1 is in ANY record of Table2 Phone1 or Table2 Phone 2, you want to remove Table1 Phone1? And the same for Table1 Phone2?

    I expect after those actions, any records in Table1 that result in Null in both fields can simply be deleted.
    Yes, this is exactly what I want to do.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Try:

    UPDATE Table1 SET Phone1 = Null WHERE Not IsNull(DLookup("Phone1","Table2")) Or Not IsNull(DLookup("Phone2","Table2"));

    UPDATE Table1 SET Phone2 = Null WHERE Not IsNull(DLookup("Phone1","Table2")) Or Not IsNull(DLookup("Phone2","Table2"));

    Nested subqueries might work as well as the DLookup but this was faster off top of my head.


    DELETE FROM Table1 WHERE IsNull(Phone1) AND IsNull(Phone2);
    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.

  7. #7
    garrettgivre is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    Try:

    UPDATE Table1 SET Phone1 = Null WHERE Not IsNull(DLookup("Phone1","Table2")) Or Not IsNull(DLookup("Phone2","Table2"));

    UPDATE Table1 SET Phone2 = Null WHERE Not IsNull(DLookup("Phone1","Table2")) Or Not IsNull(DLookup("Phone2","Table2"));

    Nested subqueries might work as well as the DLookup but this was faster off top of my head.


    DELETE FROM Table1 WHERE IsNull(Phone1) AND IsNull(Phone2);

    This may be a stupid question, but where would I enter this?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You can use the query builder to help build query objects. Can also open query builder in SQL View and copy/paste the sql statement. Should be 3 query objects then just run them.

    Ooops, I forgot part of the DLookup

    UPDATE Table1 SET Phone1 = Null WHERE Not IsNull(DLookup("Phone1","Table2", "Phone1='" & [Phone1] & "'")) Or Not IsNull(DLookup("Phone2","Table2", "Phone1='" & [Phone1] & "'"));

    UPDATE Table1 SET Phone2 = Null WHERE Not IsNull(DLookup("Phone1","Table2", "Phone1='" & [Phone2] & "'")) Or Not IsNull(DLookup("Phone2","Table2", "Phone1='" & [Phone2] & "'"));
    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.

  9. #9
    garrettgivre is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7
    Quote Originally Posted by June7 View Post
    You can use the query builder to help build query objects. Can also open query builder in SQL View and copy/paste the sql statement. Should be 3 query objects then just run them.

    Ooops, I forgot part of the DLookup

    UPDATE Table1 SET Phone1 = Null WHERE Not IsNull(DLookup("Phone1","Table2", "Phone1='" & [Phone1] & "'")) Or Not IsNull(DLookup("Phone2","Table2", "Phone1='" & [Phone1] & "'"));

    UPDATE Table1 SET Phone2 = Null WHERE Not IsNull(DLookup("Phone1","Table2", "Phone1='" & [Phone2] & "'")) Or Not IsNull(DLookup("Phone2","Table2", "Phone1='" & [Phone2] & "'"));
    This is exactly what I needed! Thank you for making it so easy to understand.

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

Similar Threads

  1. Unmatched Addresses Query
    By #1Newbie in forum Queries
    Replies: 3
    Last Post: 06-03-2013, 06:54 AM
  2. Unmatched Query Qizard.
    By deepanadhi in forum Queries
    Replies: 7
    Last Post: 05-15-2012, 03:50 AM
  3. Query and unmatched records
    By jlgray0127 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 05:56 PM
  4. Replies: 1
    Last Post: 12-08-2011, 01:52 PM
  5. Unmatched query to lookup part of the field
    By DMetcalfe in forum Queries
    Replies: 15
    Last Post: 10-08-2010, 05:30 AM

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