Find groups of records where a Boolean field is False

[Ragnar Midtskogen]

Hello,

I have a table of diagnoses where one field contains the specimen ID. It is
an ODBC linked table to SQL Server.
There may be more than one records for a specimen, where one record is
supposed to have a Boolean field called Final is set to True.
For some unknown reason some specimens none of the records have this field
set to True.
I need to identify these specimens and have trouble getting the results I am
looking for.
I have written the following queries, the second selecting the results from
the first, in order to select the records with False final diagnosis:

Query 1: SpecimenGroups:
SELECT Diagnoses.SpecimenID AS SpmID, First(Diagnoses.DateReviewed) AS
DateRev, First(Diagnoses.ReviewerInitials) AS RevInitl,
Count(Diagnoses.FinalDiagnosis) AS FinDiagn, Count(Diagnoses.FirstDiagnosis)
AS FirstDiagn
FROM Diagnoses
GROUP BY Diagnoses.SpecimenID;

Query 2: MissingFinals:
SELECT SpecimenGroups.AccNum, SpecimenGroups.SpmCount,
SpecimenGroups.DateRev, SpecimenGroups.RevInt, SpecimenGroups.FinDiag,
SpecimenGroups.FirstDiag
FROM SpecimenGroups
WHERE (((SpecimenGroups.FinDiag)=0));

This gives me all the specimens I am looking for, I think.

Since I need to show all the records for each specimen I had planned to join
the second query with the table Diagnoses.
However, this gives me an error message that the ODBC call failed, the Sum
aggregate function can not take a bit data type as argument.

HELP!

Any help would be appreciated, there must be a simpler way of doing this!

Ragnar

 

[John Spencer]

With small record sets the following will probably give you what you are
looking for. Not In can be quite slow, especially with large data sets.

SELECT D.*
FROM Diagnoses as D
WHERE SpecimenID NOT IN
(SELECT SpecimenID
FROM Diagnoses
WHERE FinalDiagnosis = True)
ORDER BY D.SpecimenID


The following should be faster, but it is more complex

SELECT D.*
FROM Diagnoses as D
WHERE D.SpecimenID IN
(SELECT Da.SpecimenID
FROM Diagnoses as Da LEFT JOIN
(SELECT SpecimenID
FROM Diagnoses as DB
WHERE FinalDiagnosis = True) as DC
ON Da.SpecimenID = DC.SpecimenID
WHERE DC.SpecimenID is Null)
ORDER BY D.SpecimenID, D.DateReviewed

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ragnar Midtskogen]

Thank you John,

I figured something like that but I never quite got the hang of subqueries.
It looks like I will need to use the complex one, the simple one has been
running for over 25 minutes in Query Analyzer on the server!
The table has about 70 K records

One question: are either of these query results updatable?
I need to display the results in a form and let the user update some
records.

Ragnar

 

[John Spencer]

They both should be updatable. Note that all the complexity is in the
subquery in the where clause which only identifies which records to return.
The query is basically the same as

SELECT Diagnoses.*
FROM Diagnoses
WHERE SpecimenId IN ("a12345","B5678")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ragnar Midtskogen]

Thank you John,

The simpler query ran 2 hours 1 minute, the other query ran 1 hour 40
minutes, on the server, but they both gave the same number of .records as my
grouping query when I count the number of records for each specimen.

Ragnar

 

[Ragnar Midtskogen]

John,

Since the both queries you gave takes a long time to run, I tried using my
original queries and have the second one create a table, with a primary
key..
Then I joined the Diagnoses table to that with an inner join. The whole
operation takes less than one minute, and the query is updatable, so I can
base my form on it and allow users to edit the info.

Ragnar