How to find missing final diagnosis record

[Ragnar Midtskogen]

Hello,

I have an SQL Server 2000 DB application with an Access 2000 front end.

There are two tables, one for specimens and another for diagnoses. The two
tables have a one to many relationship, there may be more than one diagnosis
record for each specimen, but one diagnosis record will be designated as the
final one by setting a bit field FinalDiagnosis (Represented as 0 or -1 in
Access). Both tables contain a specimen id field to link diagnoses records
to specimen records.

I am trying to find any specimens which do not have a record marked as the
final diagnosis. I thought it would be easy, but so far I have not found a
solution.

Any comments would be appreciated.

Ragnar

 

[Amy Blankenship]

First create a query on your diagnosis table searching for all records where
finalDiagnosis = -1

Now, join your specimens table to the new query (I guess on SpecimenID or
whatever the relationship is. Double-click the join line and select "All
records in specimens and only records in Diagnosis where join field is
equal".

Now, drag all of your information fields down from the Specimen table and
the FinalDiagnosis field from the Diagnosis table. In the Criteria row for
FinalDiagnosis type <> -1

HTH;

Amy

 

[Ragnar Midtskogen]

Thank you Amy,

I am afraid that does not work. The final query returns no records, which
makes sense to me, because the diagnosis table query already filtered out
the ones where the FinalDiagnosis <>-1.

Just to make sure, I entered a few records in the diagnosis table, for
specimens who had no associated records in the diagnosis table, with 0 in
the FinalDiagnosis field, and they did not show up in the results of the
final query.

If I use an 'Is Null' criteria instead of <>-1 for the FinalDiagnosis field,
I get all the specimen records which have no related records in the
diagnosis table, but I get some other records as well.

I ran a query with a left join of the specimen table with the diagnosis
table with a criteria of FinalDiagnosis 'Is Null' and got 197 records. This
gives the number of specimen records with no matching records in the
diagnosis table.

The second query you suggested, but with an 'Is Null' criteria for the
FinalDiagnosis field returns 488 records.

Any ideas?
Ragnar

 

[John Spencer]

Ragnar,
Amy had it almost correct. Using her method try the criteria IS NULL
instead of <> - 1

Build a query on the Diagnosis table that returns only records that have
final diagnosis checked and save that as qHaveFinalDx

Now using that QUERY and your SPECIMEN table use the Find Unmatched query
wizard you should be able to build the table.
The SQL would end up looking like
SELECT S.SpecimenID
FROM SpecimenTable as S LEFT JOIN
qHaveFinalDx
ON S.SpecimenId = qHaveFinalDx.SpecimenId
WHERE qHaveFinalDx.SpecimenID is Null

Another way to do this is with a subquery.
SELECT S.SpecimenID
FROM SpecimenTable as S
WHERE S.SpecimenID Not In
(SELECT D.SpecimenID
FROM DiagnosisTable as D
WHERE D.FinalDiagnosis = True)

Faster (if your table and field names do NOT have spaces in them)
SELECT S.SpecimenID
FROM SpecimenTable as S LEFT JOIN
(SELECT D.SpecimenID
FROM DiagnosisTable as D
WHERE D.FinalDiagnosis = True) as FinalDx
ON S.SpecimenId = FinalDX.SpecimenId
WHERE FinalDX.SpecimenID is Null

 

[Ragnar Midtskogen]

Thank you John,

Amy had it almost correct. Using her method try the criteria IS NULL
instead of <> - 1

I tried that too, but I thought I go too many records. But when I started
checking individual specimens did not find any incorrect results, at least
not yet, so I guess the result is correct.
The result also includes the specimens that do not have related records in
the diagnosis table, and that account for almost half of them.

Ragnar

 

[John Spencer]

If you want to eliminate those that have no Dx records at all you might try

SELECT DISTINCT S.SpecimenID
FROM (SpecimenTable as S INNER JOIN DiagnosisTable as Dx
ON S.SpecimenID = Dx.SpecimenID)
LEFT JOIN
(SELECT D.SpecimenID
FROM DiagnosisTable as D
WHERE D.FinalDiagnosis = True) as FinalDx
ON S.SpecimenId = FinalDX.SpecimenId
WHERE FinalDX.SpecimenID is Null

or if it needs to be updatable, you might try this version
SELECT S.SpecimenID
FROM SpecimenTable as S LEFT JOIN
(SELECT D.SpecimenID
FROM DiagnosisTable as D
WHERE D.FinalDiagnosis = True) as FinalDx
ON S.SpecimenId = FinalDX.SpecimenId
WHERE FinalDX.SpecimenID is Null
AND Not in
(SELECT SpecimenID
FROM DiagnosisTable)

 

[Ragnar Midtskogen]

Thank you John,

That works fine, the numbers check out.
One of these days I am going to dig deeper into SQL, the problem is always
that I need to get something done by yesterday, preferably -)).

Ragnar

 

[Ragnar Midtskogen]

Hi John,

Decided to use the updatable one, but I get an error message: missing
operator, and the IN is highlighted.
Looks OK to me, but I have not used that constract much.

Ragnar

 

[John Spencer]

Forgot to say which field I was applying the criteria against. I think the
following is correct, but can't test it.

or if it needs to be updatable, you might try this version
SELECT S.SpecimenID
FROM SpecimenTable as S LEFT JOIN
(SELECT D.SpecimenID
FROM DiagnosisTable as D
WHERE D.FinalDiagnosis = True) as FinalDx
ON S.SpecimenId = FinalDX.SpecimenId
WHERE FinalDX.SpecimenID is Null
AND S.SpecimenID Not in
(SELECT SpecimenID
FROM DiagnosisTable)

 

[Ragnar Midtskogen]

Thank you John,

That works.
I had a little trouble showing some other columns from the DiagnosisTable
but I got it to work.
I first tried adding them in the first select as D.Somefield, D.SomeOther
field, etc. but than I was prompted for them, so they were treated as
arguments.
When I added them in the first subqery the query ran fine, but none of the
columns showed up.
Then, I tried treating them as fields of the first subquery, selecting them
in the first select, but with DFinalDX.Somefield, etc, but that gave an
error message that they were not valid fields.
It finally occurred to me that I had to combine the two last methods, first
selecting them in the first subquery from the D table, and also selecting
them in the first select from the FinalDX table.
The solution was pretty obvious once I thought about it.

Ragnar