Show records based on duplicates between 2 tables

[gold007eye]

Ok.. I have been racking my brain for hours trying to figure out how to do this. Maybe someone here can help me figure this out.

I have 2 tables. "AR94" & "RPIN"
I need a query to look at the "Provider_PIN" field of both tables in order to find duplicates between that field in both separate tables at which point I need it to say If a duplicate provider_pin exists in the AR94 table & the RPIN table Then remove that provider_pin from the "RPIN" table.

The final result should be that the only records showing in the "RPIN" table would be those provider_pins' that were NOT found in both tables.

Starting Example (Before query):
AR94 Table:
-----------
1234
12345
123456
1234567
12345678

RPIN Table:
-----------
1234
4567
45678
12345678
456789

Results that should be achieved (after running query):
RPIN Table:
4567
45678
456789

Thereby deleting the 1234 & 12345678 values from the RPIN table leaving only records that were NOT found in both tables.

Please help me as I am trying to get this done for a project and can't figure it out.. Thanks.

 

[RuralGuy]

Have you tried using the "Find Duplicates" query wizard?

 

[gold007eye]

Yes, but the problem is I don't need the duplicates per se. I need to show everything EXCEPT the duplicates found between the 2 tables.

 

[RuralGuy]

Then look at the Not In() clause of sql.

 

[gold007eye]

Could you show me an example of how to use the Not In() clause?

This is what I am trying, but it is asking for input when I run the query.

SELECT *
FROM RPIN
WHERE (((RPIN.Provider_PIN) Not In (AR94.Provider_PIN)))
ORDER BY RPIN.Provider_PIN;

 

[Rabbie]

Have you looked at the unmatched Query Wizard in Access

 

[gold007eye]

Yes I tried that also, but you can only do that based on 1 table not 2.

 

[Rabbie]

I have done it for in Table A and not in Table B and for In table B and not in Table A so I don't understand what you mean.