HELP with Duplicate data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

I need help with logic to delete (or not include) some duplicate values in a
table. Here is a sample of the table.

Claim_ID Claim_Seq Tl_Chg Tl_Paid Disall_Amt Disall_Rea Disall_Txt

010002 14 $12,000 0 $12,000 DDC
Duplicate Claim
010002 1 $12,000 0 $12,000
Not Assigned
010003 4 $19,250 0 $19,250 Y41
Deny Pre Auth
010003 1 $19,250 0 $19,250
Not Assigned

If a Claim_ID and Tl_Chg amount is duplicated AND one of the Disall_Txt is
"Not Assigned", I don't want the value for "Not Assigned" to be included in
the calculations. In essence, that is sum of the entire claim that was
disallowed and the other lines are detail of the claim. So if the "Not
Assigned" portion is included, it would appear that $38,500 was denied and
not $19,250.

I want the logic to be something like: If the Claim_ID and Tl_Chg are
duplicate AND one of the Disall_Text is "Not Assigned", set the "Not
Assigned" value to 0 and keep the other value.
 
Ess said:
Greetings,

I need help with logic to delete (or not include) some duplicate values in a
table. Here is a sample of the table.

Claim_ID Claim_Seq Tl_Chg Tl_Paid Disall_Amt Disall_Rea Disall_Txt

010002 14 $12,000 0 $12,000 DDC
Duplicate Claim
010002 1 $12,000 0 $12,000
Not Assigned
010003 4 $19,250 0 $19,250 Y41
Deny Pre Auth
010003 1 $19,250 0 $19,250
Not Assigned

If a Claim_ID and Tl_Chg amount is duplicated AND one of the Disall_Txt is
"Not Assigned", I don't want the value for "Not Assigned" to be included in
the calculations. In essence, that is sum of the entire claim that was
disallowed and the other lines are detail of the claim. So if the "Not
Assigned" portion is included, it would appear that $38,500 was denied and
not $19,250.

I want the logic to be something like: If the Claim_ID and Tl_Chg are
duplicate AND one of the Disall_Text is "Not Assigned", set the "Not
Assigned" value to 0 and keep the other value.
Click to expand...

To exclude, is it not as simple as saying
WHERE Disall_Txt <> 'Not Assigned'
and if not, could you elaborate on why?
 
"Not Assigned" could be assigned to other values as well; therefore I don't
want to exclude it completely. I only want to exclude it if the Claim_ID and
Tl_Chg are equal and "Not Assigned" is one of the Disall_Txt.

So the expression should be something like:
IIf([Claim_Id]>1 & [Disall_Txt]="Not Assigned", 0, [Tl_Chg]). I think I
just answered my own question. Let me go try my own logic.
 
Back
Top