Finding a match

[Guest]

I have 2 tables.
Table Transactions contains data for patients medical visits.
[DATE],[NAME],[TREATMENT CODE],[DIAGNOSIS]

Table Approved Diagnosis contains a list of approved Diagnosis codes for
each Treatment Code.
[TREATMENT CODE],[DIAGNOSIS]

Each Treatment Code can have many approved diagnosis codes.

I need to return any records in the transaction table whose TREATMENT CODE
does not have a corresponding APPROVED DIAGNOSIS code in the Approved
Diagnosis table.

What would be the best approach to accomplish this?

 

[Dale Fye]

Stefan,

Select T1.TransDate, T1.PatientName, T1.[Treatment Code], T1.Diagnosis,
FROM tbl_Transactions T1
LEFT JOIN tbl_Approved_Diagnosis T2
ON T1.[Treatment Code] = T2.[Treatement Code]
WHERE T2.[Treatment Code] is NULL

OR

Select TransDate, PatientName, [Treatment Code], Diagnosis,
FROM tbl_Transactions T1
WHERE [Treatment Code]
NOT IN (SELECT DISTINCT [Treatment Code] FROM tbl_Diagnosis)

Unsure which of these will be quicker (you should test them in your
database), because the speed will depend on the number of records and your
indexes.

Notes:
1. [Date] is a reserved word in Access, I strongly urge you to make the
field name more descriptive of what the date represents (TransDate).
2. Patient Name is a bad field to use in your Transactions table. What
makes you think you will never have two patients with the same name. You
need a patients table that has a Patient ID as its primary key, which is
what you should put in every other table where you need to represent your
patient.
3. The left join in the query above tells access to select all of the
records from the transaction table and match them to whatever records in the
diagnosis table the can match to, and to include all those records from the
transactions that don't have a match in the diagnosis table. The WHERE
clause restricts the result set to only those records where there is no
matching value in the diagnosis table

HTH
Dale