How To Check Two Other Tables Before Appending Records?

[CiViCChiC79]

Hi There!

I am having trouble finding an answer to my question. I know how to append records to a table without getting a key violation. In this case I need to check TWO other tables for matching keys before appending.

I tried:
Criteria: Not In (SELECT tblPLDenialReview1_Initial.AppNumber FROM tblPLDenialReview1_Initial) AND Not In (SELECT tblPLDenialsQueue_Tier001.AppNumber FROM tblPLDenialsQueue_Tier001)

But that only allowed 3 records out of 15,000 to post. If I use OR in the above statement, of course it works just like the criteria isn't specified at all. A given record will never exist in both tables at the same time.

Any help you can offer is greatly appreciated!!

Thanks, CiViCChiC79

 

[Russell Hauf]

Your criteria looks correct. To check how many wouldn't post, you could try this instead:

In (SELECT tblPLDenialReview1_Initial.AppNumber FROM tblPLDenialReview1_Initial) OR In (SELECT tblPLDenialsQueue_Tier001.AppNumber FROM tblPLDenialsQueue_Tier001)

By chance do you have any other criteria in your query?

 

[CiViCChiC79]

Hi Russell,

Thanks, I will try that. I only have one other field in this query that I am checking. It is a numeric category field 1 through 5.

Regards, CiViCChiC79

 

[CiViCChiC79]

Actually, I realized I need to check each and every table for Tiers 1 through 5 and the final resting place (tblPLDenialReview1_Initial) because if a Tier rating changes and the record is still in one of the "Tiered Buckets", it would still import that record. My users don't ever want to see a duplicate application ever. So I have decided to just create another table that houses just the primary key for all Tiers and run my Not In criteria against that. Thanks so much for your help!!

 

[Russell Hauf]

Good idea. Happy Wednesday!

 

[CiViCChiC79]

Thanks You Too!