Please help extracting records

[Al]

I have a table with the following values:

PatID TestDate Type Value Range Na_Yes_No
2163 7/10/1995 NA 143 145 0
2163 10/2/1995 NA 147 145 1
2163 11/27/1995 NA 140 145 0
2163 7/1/1996 NA 144 145 0
2163 7/10/1996 NA 146 148 0
2163 7/18/1996 NA 151 145 1
2163 7/26/1996 NA 146 145 1
2163 8/5/1996 NA 144 145 0
2163 8/22/1996 NA 146 145 1
2163 9/9/1996 NA 150 147 1
2163 9/16/1996 NA 145 145 0
2163 10/1/1996 NA 147 145 1
2163 10/17/1996 NA 142 145 0
2163 2/18/1997 NA 141 145 0
2163 3/3/1997 NA 149 145 1
2163 3/11/1997 NA 146 145 1
2163 3/17/1997 NA 143 145 0
2163 3/26/1997 NA 149 145 1
2163 3/31/1997 NA 143 145 0
2163 4/14/1997 NA 145 145 0
2163 5/1/1997 NA 148 145 1
2163 5/8/1997 NA 146 145 1
2163 5/19/1997 NA 150 145 1
2163 5/21/1997 NA 142 145 0

I am interested in extracting the records that has 1 in the ("Na_Yes_No"
field) with the following scenario:
(0 1 1 0) or (0 1 1 1 0), I do not care about records that has 1 in the
following Scenario: (0 1 0). the "1" must be repeated at least 2 times to be
of interest to me.

in the above example there should be 9 records that I am interested in
(flagging/extracting). these 9 records are for the following dates:
7/18/1996
7/26/1996
8/22/1996
9/9/1996
3/3/1997
3/11/1997
5/1/1997
5/8/1997
5/19/1997
can someone help me with the code on this?
thanks
Al

 

[John Griffiths]

You can use sub queries.

Something like the following untested query

SELECT ptt.*
FROM PatTestTable ptt
WHERE ptt.PatID IN (
SELECT sub.PatID, COUNT(Na_Yes_No)
FROM PatTestTable sub
HAVING COUNT(sub.Na_Yes_No) > 1
)

Regards John

 

[Al]

I am getting the following error message:
"you have written a subquery that can return more than one field without
using the
EXISTS reserved word in the Main query FROM clause. Revise the select
statment in the subquery to request only one field"
any idea?
thanks

 

[Duane Hookom]

Taking John's idea a bit further:
SELECT ptt.*
FROM PatTestTable ptt
WHERE ptt.PatID IN (
SELECT sub.PatID
FROM PatTestTable sub
GROUP BY sub.PatID
HAVING COUNT(sub.Na_Yes_No) > 1
)

 

[Al]

Duane, Thank you for the help. I am not able to run the query this way, for
some reason I keep getting an error message saying that " Could not find file
E:\qryNa_Iv_Na_Yes_No.mdb".
here is the statment after changing to the real query name which is
(qryNa_Iv_Na_Yes_No)

SELECT *
FROM qryNa_Iv_Na_Yes_No
WHERE (((qryNa_Iv_Na_Yes_No.PatientID) In (SELECT sub.PatientID FROM
qryNa_Iv_Na_Yes_No.PatientID sub GROUP BY sub.PatientID HAVING
COUNT(sub.Na_Yes_No) > 1)));

For some reason it keeps giving me the error messages. Another question, as
I stated in my example I am only interested in one scenario where there is
more than 1 (2 or more) between to zeros. wouldn't this give a total some of
ones per patientID?
thanks
Al

 

[John Griffiths]

? For some reason it keeps giving me the error messages. Another question,
as
? I stated in my example I am only interested in one scenario where there is
? more than 1 (2 or more) between to zeros. wouldn't this give a total some
of
? ones per patientID?
? thanks
? Al

The query is designed to show those results
from the table that have more than a single
value of '1' in the Na_Yes_No field.

You would have to explain a little more,
about the real world problem you are trying to model
and whether other sequences would also
need to be recorded say if in all 20 runs
of the same test and they were all 1 would
you still need to show them or count the sequence
as statistically valid.

Relational tables do not come with inbuilt processing
as to record numbers; so such things as previous
record and next record have to be supplied and
serviced by the designer.

By creating a privious id and count fields you can do
what you want but with cirtain caveats as to what
makes a valid sequence.

Regards John

 

[John Griffiths]

? Duane, Thank you for the help. I am not able to run the query this way,
for
? some reason I keep getting an error message saying that " Could not find
file
? E:\qryNa_Iv_Na_Yes_No.mdb".
? here is the statment after changing to the real query name which is
? (qryNa_Iv_Na_Yes_No)
?
? SELECT *
? FROM qryNa_Iv_Na_Yes_No
? WHERE (((qryNa_Iv_Na_Yes_No.PatientID) In (SELECT sub.PatientID FROM
? qryNa_Iv_Na_Yes_No.PatientID sub GROUP BY sub.PatientID HAVING
? COUNT(sub.Na_Yes_No) > 1)));
?

Starting from scratch in a new query window build the first part of the
query
? SELECT *
? FROM qryNa_Iv_Na_Yes_No
without using the SQL page, query builder only.
Do you get the same error message?

Regards John

 

[Al]

No, here is what we are trying to do:
we are trying to find out the "Na" level (per patient) that is higher than
the normal upper range, ONLY if this high level lasted more than one day.
I made a calculated field to calculate 0 (if Na value was less than Upper
range or 1 if Na value was higher than upper range).
The query name is: qryNa_Iv_Na_Yes_No
the fields names are:
PatientID
TestDate
TestType(we selected Na)
Value (Na level)
Range (UpperRange)
Na_Yes_No (this is the field that has the 0 and the 1).
what I want to do is to flag the records that has 1s only in the following
scenario:
Date1 (Na < Upper range, i.e. Na_Yes_No = 0)
Date2 (Na> Upper range, I.e. Na_Yes_No = 1)
Date3 (Na> Upper range, I.e. Na_Yes_No = 1)
Date4 (Na> Upper range, I.e. Na_Yes_No = 1)
Date5 (Na< Upper range, I.e. Na_Yes_No = 0)
Here I want to flag the records of date 2,3, and 4 using for example a
calculated field that will place the word "yes" only in these cases. The
sample data that I included in my early message is real you can use it. That
is how the query looks. I just shortened some of the fields names. Again
John, I do appreciate your help and I hope that I was clear enough on what I
am trying to do.
thanks
Al

 

[John Griffiths]

? No, here is what we are trying to do:
? we are trying to find out the "Na" level (per patient) that is higher than
? the normal upper range, ONLY if this high level lasted more than one day.
? I made a calculated field to calculate 0 (if Na value was less than Upper
? range or 1 if Na value was higher than upper range).
?

-------------------------------------------------

You have.
PatientID
TestDate
TestType(we selected Na)
Value (Na level)
Range (UpperRange)
Na_Yes_No (this is the field that has the 0 and the 1).

-------------------------------------------------
Needed
PreviousReading Number=Long
SequenceCount Number=Long/Integer/Byte depending on taste, default=0, nulls
not allowed.

-------------------------------------------------
Also Needed
PatientTestID (AutoNumber ?)
You can use PatientID/TestDate/TestType if it is the primary key.

-------------------------------------------------
Required update query to initialise values.
UPDATE PatientTest
SET SequenceCount = 0, PreviousReading = 0 ;

-------------------------------------------------
Set-up the link to the previous reading
UPDATE PatientTest
SET PreviousReading =
SELECT MAX(PatientTest1.ID)
FROM PatientTest PatientTest1
WHERE PatientTest1.PatientID = PatientTest.PatientID
AND PatientTest1.TestDate = PatientTest.TestDate
AND PatientTest1.TestType=PatientTest.TestType
AND PatientTest.ID > PatientTest1.ID ;

-------------------------------------------------
Set count if start of a sequence
UPDATE PatientTest
SET SequenceCount = 1
WHERE Na_Yes_No = 1 ;

-------------------------------------------------
Build on the initial sequence
UPDATE
PatientTest INNER JOIN PatientTest AS PatientTest1
ON PatientTest.PreviousReading = PatientTest1.ID
SET
PatientTest.SequenceCount = PatientTest.SequenceCount + 1
WHERE
PatientTest1.Na_Yes_No = 1
;

-------------------------------------------------
Specific update required for 1st day
UPDATE
PatientTest INNER JOIN PatientTest AS PatientTest1
ON PatientTest.PreviousReading = PatientTest1.ID
SET
PatientTest1.SequenceCount = PatientTest1.SequenceCount + 1
WHERE
PatientTest1.Na_Yes_No = 1
AND PatientTest.Na_Yes_No = 1
AND PatientTest1.SequenceCount = 1
;

-------------------------------------------------
This is as far as you request
SequenceCount = 0, 1, 2
the 2's are the records you require

SELECT *
FROM PatientTest
WHERE SequenceCount = 2 ;

The generation of the sequence count beyond 2 is
only a little more work, requiring a field to mark when
the calculation is complete during the generation phase.

If you do not want further complicate the patient test table
you would create a parallel table(s), only dealing with those tests
you are interested in.

This is a bit long winded even though and I have left out the
narrative version for the SQL version as I would expect the
SQL to be more concise.

Remember as I do not have a depth of knowledge of your other
tables/relationships/scale that this is a description of the
territory not a recipe to be followed.

John

 

[Al]

Hi John,
I resolved this issue by a bit different approach.
I used the sql to obtain the Previous data like you did and then I also
obtained the next date and the date after the next date. then I used dlook up
on a report to get the 1s and 0s values for each date and simply used the
following code to flag the records that I need to extrac:
Select Case Me![txtZeroOnes]
case "0,1,1,0", "1,1,0,1", "0,1,1,1" "1,1,1,0", "1,1,0,0"
me![Extract] = "yes"
case else
me![Extract] = ""
end select
then I took all the records that are flagged "yes" and appended them to
extracted record table.
the only issue I have is that, I think that the dlookup is slowing down the
report.
Al

 

[John Griffiths]

The partial answer I have gone into
(just short of the arbitrary case).

If this is a continuous process you should
record the relevant information.

Using DLookup/DSum/DCount... is more than mere
calculation seeing as it relies upon database access.

If this information is required on every patient test the
SequenceCount can be updated on insert/update.

The easy cases are for creation.
If Na_Yes_No changes from unknown to YES then
SequenceCount = previous count +1
Previous record less than 24 hour previously otherwise 1
If Na_Yes_No changes from unknown to NO
then SequenceCount = 0


Aircode, Exists is one of the cheapest subqueries as
it doesn't need to do a complete table lookup.
SELECT * FROM Patient p WHERE EXISTS (
SELECT pt,ID
FROM PatientTest pt
WHERE pt.PatientID = p.ID
AND pt.SequenceCount > 2
{AND MORE CRITERIA HERE}
) ;

Indexable fields to improve lookup cost but increase insertion cost.
PatientID
TestDate
TestType
SequenceCount
Na_Yes_No (if you want to be complete)

Regards John

Hi John,
I resolved this issue by a bit different approach.
I used the sql to obtain the Previous data like you did and then I also
obtained the next date and the date after the next date. then I used dlook up
on a report to get the 1s and 0s values for each date and simply used the
following code to flag the records that I need to extrac:
Select Case Me![txtZeroOnes]
case "0,1,1,0", "1,1,0,1", "0,1,1,1" "1,1,1,0", "1,1,0,0"
me![Extract] = "yes"
case else
me![Extract] = ""
end select
then I took all the records that are flagged "yes" and appended them to
extracted record table.
the only issue I have is that, I think that the dlookup is slowing down the
report.
Al

? No, here is what we are trying to do:
? we are trying to find out the "Na" level (per patient) that is higher than
? the normal upper range, ONLY if this high level lasted more than one day.
? I made a calculated field to calculate 0 (if Na value was less than Upper
? range or 1 if Na value was higher than upper range).
?

-------------------------------------------------

You have.
PatientID
TestDate
TestType(we selected Na)
Value (Na level)
Range (UpperRange)
Na_Yes_No (this is the field that has the 0 and the 1).

-------------------------------------------------
Needed
PreviousReading Number=Long
SequenceCount Number=Long/Integer/Byte depending on taste, default=0, nulls
not allowed.

-------------------------------------------------
Also Needed
PatientTestID (AutoNumber ?)
You can use PatientID/TestDate/TestType if it is the primary key.

-------------------------------------------------
Required update query to initialise values.
UPDATE PatientTest
SET SequenceCount = 0, PreviousReading = 0 ;

-------------------------------------------------
Set-up the link to the previous reading
UPDATE PatientTest
SET PreviousReading =
SELECT MAX(PatientTest1.ID)
FROM PatientTest PatientTest1
WHERE PatientTest1.PatientID = PatientTest.PatientID
AND PatientTest1.TestDate = PatientTest.TestDate
AND PatientTest1.TestType=PatientTest.TestType
AND PatientTest.ID > PatientTest1.ID ;

-------------------------------------------------
Set count if start of a sequence
UPDATE PatientTest
SET SequenceCount = 1
WHERE Na_Yes_No = 1 ;

-------------------------------------------------
Build on the initial sequence
UPDATE
PatientTest INNER JOIN PatientTest AS PatientTest1
ON PatientTest.PreviousReading = PatientTest1.ID
SET
PatientTest.SequenceCount = PatientTest.SequenceCount + 1
WHERE
PatientTest1.Na_Yes_No = 1
;

-------------------------------------------------
Specific update required for 1st day
UPDATE
PatientTest INNER JOIN PatientTest AS PatientTest1
ON PatientTest.PreviousReading = PatientTest1.ID
SET
PatientTest1.SequenceCount = PatientTest1.SequenceCount + 1
WHERE
PatientTest1.Na_Yes_No = 1
AND PatientTest.Na_Yes_No = 1
AND PatientTest1.SequenceCount = 1
;

-------------------------------------------------
This is as far as you request
SequenceCount = 0, 1, 2
the 2's are the records you require

SELECT *
FROM PatientTest
WHERE SequenceCount = 2 ;

The generation of the sequence count beyond 2 is
only a little more work, requiring a field to mark when
the calculation is complete during the generation phase.

If you do not want further complicate the patient test table
you would create a parallel table(s), only dealing with those tests
you are interested in.

This is a bit long winded even though and I have left out the
narrative version for the SQL version as I would expect the
SQL to be more concise.

Remember as I do not have a depth of knowledge of your other
tables/relationships/scale that this is a description of the
territory not a recipe to be followed.

John
-------------------------------------------------


there
is