Remove Only Part of Record Using Unmatched Query

[garrettgivre]

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.

[June7]

Would have to run an UPDATE query to set the field to null.

[garrettgivre]

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?

[June7]

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.

[garrettgivre]

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.

[June7]

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

[garrettgivre]

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?

[June7]

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] & "'"));

[garrettgivre]

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.