Return non matched values

[Guest]

I have 2 tables related to medical records.

The first table has Treatment Codes(CPT) and related Diagnosis(DX) codes.
Sample Data: Transaction Table
CHART DATE CPT DX
STEFAN 1/1/2005 97110 800
STEFAN 1/1/2005 97140 847.0
VICT 2/1/2005 97110 724.0
VICT 2/1/2005 97140 724.0

The Second table Has a list of approved (by medicare) Diagnosis codes. Each
CPT has a list of specific Diagnosis codes that are related.
Sample Data: Approved Diagnosis
CPT DX
97110 724.0
97110 724.1
97110 724.2
97140 847.0
97140 847.1
97140 847.2

I need to return any records from the Transaction table where the
Diagnosis(DX) for the Treatment Code(CPT) is not listed under that same CPT
on the Approved Diagnosis Table.
Another words the following record would be returned because the Diagnosis
is not listed as approved for CPT 97140 in the Approved Diagnosis table.

VICT 2/1/2005 97140 724.0

Again I need to return all records where the diagnosis is not approved.

I did post a similar question earlier however I couldn't get the answer to
work. I don't believe I explained myself clearly. Sorry for the double
posting but I can really use the help.

 

[Michel Walsh]

Hi,


SELECT a.*
FROM Transactions As a LEFT JOIN Diagnosis As b
ON a.CPT=b.CPT AND ABS(a.DX-b.DX) <= 0.01
WHERE b.CPT IS NULL




You can try the easier formulation:

SELECT a.*
FROM Transactions As a LEFT JOIN Diagnosis As b
ON a.CPT=b.CPT AND a.DX = b.DX
WHERE b.CPT IS NULL


but it is preferable to test floating point values, DX, with a tolerance. If
DX are string, you can safely use the simplest formulation.



Hoping it may help,
Vanderghast, Access MVP