Hello,
I am attempting to create an automated audit of two sets of data—one that is internal data housed on our operating system and an external data set sent to us monthly. The tables I am using are as follows:
--Current_AA_FCA_PROVIDERS
This table contains the data (names, address, id #’s, etc) currently held for a group known as FCA within our operating system.
--FCA_Providers_Upper
This table contains data that has been supplied to us from the group. It will contain additions, changes, and deletions that our in house data needs to mirror
--Unmatched_IRS_Number_AA_to_FCA
This table has been created to identify those items between the Current_AA_FCA_PROVIDERS table and the FCA_Providers_Upper table where the NPI #’s equal each other, but the IRS_NBRs do not
--Unmatched_IRS_Number_FCA_to_AA
This table has been created to identify those items between the FCA_Providers_Upper table and the Current_AA_FCA_PROVIDERS table where the NPI #’s equal each other, but the IRS_NBRs do not

The driving force behind this process is that the NPI #’s and the IRS #’s on both tables need to match. Sometimes there are instances where the data from the group contains the same IRS # twenty times, but each NPI # is different.

To reduce the amount of data that has to be manually “touched”, I would like to eliminate the data that has been identified in a previous query—If an NPI is missing from either table—it has already been identified.

The way I have my queries structured now—and why I need help—is twofold: a) if the NPI does not exist on both tables, it wants to report this —I do not need this data. b) if the IRS # matches, it drops off the report without looking at the NPI. I could have an IRS # of 123 listed 5 times and NPI # of 6, 7, 8, 9, on both tables but 10 only exist on the data being sent by the group. Because the IRS # matches, the NPI 10 will not report. I need it to recognize that NPI 10 and IRS 123 needs to report because it does not exist on both.

I have two tables that I run the following two queries for:

SELECT * INTO Unmatched_IRS_Number_AA_to_FCA


FROM Current_AA_FCA_PROVIDERS AS A
WHERE (((Exists (select FCA_Providers_Upper.*
from FCA_Providers_Upper
where A.NPI = NPI
and A.IRS_NBR = IRS_NBR))=False));

SELECT * INTO Unmatched_IRS_Number_FCA_to_AA
FROM FCA_Providers_Upper AS A
WHERE (((Exists (select Current_AA_FCA_PROVIDERS.*
from Current_AA_FCA_PROVIDERS
where A.NPI = NPI
and A.IRS_NBR = IRS_NBR))=False));

I then need to "filter" this data and that is where I need assistance. My current code is this:
SELECT Unmatched_IRS_Number_FCA_to_AA.YMDEFF_D, Unmatched_IRS_Number_FCA_to_AA.YMDEND_D, Unmatched_IRS_Number_FCA_to_AA.IRS_NBR, Unmatched_IRS_Number_FCA_to_AA.PROV_NBR_OLD, Unmatched_IRS_Number_FCA_to_AA.LASTNAME, Unmatched_IRS_Number_FCA_to_AA.newlastname, Unmatched_IRS_Number_FCA_to_AA.FIRSTNAME, Unmatched_IRS_Number_FCA_to_AA.newfirstname, Unmatched_IRS_Number_FCA_to_AA.DEGREE, Unmatched_IRS_Number_FCA_to_AA.SEX, Unmatched_IRS_Number_FCA_to_AA.YMDBIRTH, Unmatched_IRS_Number_FCA_to_AA.NPI, Unmatched_IRS_Number_FCA_to_AA.LANG01, Unmatched_IRS_Number_FCA_to_AA.LANG02, Unmatched_IRS_Number_FCA_to_AA.LANG03, Unmatched_IRS_Number_FCA_to_AA.LANG04, Unmatched_IRS_Number_FCA_to_AA.ADDR_WHO, Unmatched_IRS_Number_FCA_to_AA.ADDRESS1, Unmatched_IRS_Number_FCA_to_AA.CITY, Unmatched_IRS_Number_FCA_to_AA.STATE, Unmatched_IRS_Number_FCA_to_AA.ZIP, Unmatched_IRS_Number_FCA_to_AA.COUNTY, Unmatched_IRS_Number_FCA_to_AA.PHONE1, Unmatched_IRS_Number_FCA_to_AA.SPEC1, Unmatched_IRS_Number_FCA_to
_AA.LICENSE_NBR, Unmatched_IRS_Number_FCA_to_AA.NARC_NBR, Unmatched_IRS_Number_FCA_to_AA.[ACCEPT FEE], Unmatched_IRS_Number_FCA_to_AA.[SPEC (SP)], Unmatched_IRS_Number_FCA_to_AA.MEDICARE_NBR, Unmatched_IRS_Number_FCA_to_AA.[SUB PROG#], Unmatched_IRS_Number_FCA_to_AA.MEDICAID_NBR, Unmatched_IRS_Number_FCA_to_AA.[PRACTICE#], Unmatched_IRS_Number_FCA_to_AA.PROVIDER INTO Unmatched_IRS_Filter_FCA
FROM Unmatched_IRS_Number_FCA_to_AA LEFT JOIN Unmatched_IRS_Number_AA_to_FCA ON Unmatched_IRS_Number_FCA_to_AA.[IRS_NBR] = Unmatched_IRS_Number_AA_to_FCA.[IRS_NBR]
WHERE (((Unmatched_IRS_Number_AA_to_FCA.IRS_NBR) Is Null));

It fails for the following reasons:
*if the IRS # equals each other on both tables, it does not account for the different NPI #'s and "drops off"
*if the NPI # does not exist on the second table, it includes this in the results. I do not want this in the results, because unmatched NPI #'s exist on another report (if I have to live with this, I can as I can always address it with an additional query later I supposed)

I know I can use aliases but wanted to make sure I provided thorough data--I truly only want to see the results where the NPI #'s equal and the IRS #'s are different. I will repeat the query and run it against the second query table results to obtain the same info. I have been going back to this scenario for a week trying to play with it and cannot figure it out. I'm out of my element here and would appreciate any suggestions -- just know that I'm definitely a novice at this so be gentle. (It has been suggested that I use a "where not exist" statement, but I have been unable to make that work either)

This is visual example of what I need
Current AA FCA Providers
 NPI IRS NPI IRS
abc def (this doesn't need to report because it will have been identified already)
6 123 6 123
 7 123 7 123
8 123 8 123
9 123 9 123
this needs to report) 10 123