Survey for Drug Tx database

[Piet Linden]

I have a database with a structure something like this:

Drug Protocol--(1,M)----Enrollment---(M,1)----Patient

(and then there's a ton of stuff related to Enrollment...)

Each Protocol can have many "qualification" questions, some of which
can disqualify a patient from participating in/enrolling in a
protocol. (For example, I can't give a drug that decreases kidney
function to someone with low kidney function, so I put a question in
my survey to eliminate those people.)

Okay... on to my question... How would I set up a generic structure
for this? Basically tweak Duane's At Your Survey database to include
a "knockout" answer in the Questions table, so that I could do
something like this to determine disqualified patients?

SELECT Question.ProtocolID, Question.KnockoutResponse,
Response.PatientAnswer, (Question.KnockoutResponse =
Response.PatientAnswer) As IsKnockout
FROM Question INNER JOIN Response ON ...
....

And then I could, I guess AND the responses together (because any DQ
question answered "wrong" causes a fail.)...

Am I on the right track or just on a train track?

Thanks!

Pieter

 

[Clifford Bass]

Hi Piet,

I have not looked at Duane's database, so I may be off base here. I
would think that you do something like this:

select count(*) as KnockoutResponseCount
from Question inner join Response on ...
where .... and Response.PatientAnswer = Question.KnockoutResponse

If the result is zero the patient can participant, otherwise he is
knocked out.

Hope this helps,

Clifford Bass

 

[Piet Linden]

Hi Piet,

     I have not looked at Duane's database, so I may be off base here.  I
would think that you do something like this:

select count(*) as KnockoutResponseCount
from Question inner join Response on ...
where .... and Response.PatientAnswer = Question.KnockoutResponse

     If the result is zero the patient can participant, otherwise he is
knocked out.

                         Hope this helps,

                               Clifford Bass

Thanks Cliff... I was just wondering if there were a consistent
structure I could use... I don't think there is beyond what Duane has
mapped out. For even more added fun, I was going to implement this in
SQL Server, which isn't nearly as forgiving as Access in doing
implicit conversions, because I would basically store everything as
text and then convert to whatever datatype I needed to...

 

[Clifford Bass]

Hi Piet,

You are welcome.

I could not say whether or not there is a consistent structure.
Probably just make up whatever you can to fit your situation.

I would recommend against using all text fields. They can cause a lot
of trouble and require constant type checking of values, along with the
conversion issues. You also have to deal with issues such as data coming in
from other sources that you or your successors may have no control or little
control over. They can also cause trouble for ad hoc query users who may not
know that you have done that. When they see a date or a number they will
expect it to behave like one and end up having trouble when they do not.
Think sorting of dates entered in the mm/dd/yyyy or dd-mm-yyyy formats. Why
reinvent the wheel? Just use the correct data types to begin with. Only
once that I can recall have I wished I had stored a number as a four-digit
text field. That was when a college code list from ancient main-frame days
when storage was expensive and they used four-digit codes, stored as text.
Once they needed to add beyond 9,999 colleges, they needed to do something
different. Their solution was to start using letters. My local copy of the
table used numeric fields and had to be converted to use text. A bit of a
bother because of all of the places that used the code, but not a huge one.
My $0.02 worth.

Clifford Bass

 

[Piet Linden]

Hi Piet,

     You are welcome.

     I could not say whether or not there is a consistent structure..  
Probably just make up whatever you can to fit your situation.

     I would recommend against using all text fields.  They can cause a lot
of trouble and require constant type checking of values, along with the
conversion issues.  You also have to deal with issues such as data coming in
from other sources that you or your successors may have no control or little
control over.  They can also cause trouble for ad hoc query users who may not
know that you have done that.  When they see a date or a number they will
expect it to behave like one and end up having trouble when they do not.  
Think sorting of dates entered in the mm/dd/yyyy or dd-mm-yyyy formats.  Why
reinvent the wheel?  Just use the correct data types to begin with.  Only
once that I can recall have I wished I had stored a number as a four-digit
text field.  That was when a college code list from ancient main-frame days
when storage was expensive and they used four-digit codes, stored as text..  
Once they needed to add beyond 9,999 colleges, they needed to do something
different.  Their solution was to start using letters.  My local copyof the
table used numeric fields and had to be converted to use text.  A bit of a
bother because of all of the places that used the code, but not a huge one.  
My $0.02 worth.

           Clifford Bass

Cliff,

I was hoping for a generic solution, but as Danny Lesandrini pointed
out, it may not be possible. I guess it could be done in a way
similar to the way Duane does it in At Your Survey, but that's
probably the only option. The downside to that solution is that it
requires a LOT of type conversion. Access is pretty lenient when it
comes to that, but SQL Server is much less so. I was hoping I could
maybe build the SQL on the fly and create a view/temporary table from
a function and stuff all the patients that meet the criteria into
that... then I could process from there...

Maybe it's not a common thing because it's, well, difficult or not
terribly efficient. I was just trying to figure out a way to put all
the study data into one *big* database instead of a zillion silly
little ones. Maybe the best approach was what I have - a template in
Access that connects to SQL Server to grab patients and then I can
tweak the table structures to add fields to incorporate all the
selection/DQ facts.

I was just hoping I had overlooked something obvious, but maybe not...

Thanks though.

Pieter

 

[Michael Gramelspacher]

Cliff,

I was hoping for a generic solution, but as Danny Lesandrini pointed
out, it may not be possible. I guess it could be done in a way
similar to the way Duane does it in At Your Survey, but that's
probably the only option. The downside to that solution is that it
requires a LOT of type conversion. Access is pretty lenient when it
comes to that, but SQL Server is much less so. I was hoping I could
maybe build the SQL on the fly and create a view/temporary table from
a function and stuff all the patients that meet the criteria into
that... then I could process from there...

Maybe it's not a common thing because it's, well, difficult or not
terribly efficient. I was just trying to figure out a way to put all
the study data into one *big* database instead of a zillion silly
little ones. Maybe the best approach was what I have - a template in
Access that connects to SQL Server to grab patients and then I can
tweak the table structures to add fields to incorporate all the
selection/DQ facts.

I was just hoping I had overlooked something obvious, but maybe not...

Thanks though.

Pieter

I have a sample survey here:
http://www.psci.net/gramelsp/temp/Survey Template.zip

In your case a yes answer to a survey question can be disqualifying for one drug and not
disqualifying for another drug.

In my survey a question has one correct answer.

I would add a column isdisqualifying to table SurveyDetails with values 1 or 0.
In table QuestionAnswers I would have answer_nbr 0 be no, 1 be yes and 2 be not applicable.
Then I think this would work.


Query: Get QualifyingRespondents

SELECT SurveyRespondents.respondent_id,
SurveyRespondents.survey_id
FROM SurveyRespondents
WHERE SurveyRespondents.survey_id = [Enter survey id:]
AND NOT EXISTS (SELECT a.respondent_id,
a.survey_id
FROM SurveyDetails
INNER JOIN SurveyResponses AS a
ON (SurveyDetails.survey_id = a.survey_id)
AND (SurveyDetails.question_nbr = a.question_nbr)
WHERE SurveyDetails.answer_required = a.answer_nbr
AND a.respondent_id = SurveyRespondents.respondent_id
AND a.survey_id = SurveyRespondents.survey_id)
GROUP BY SurveyRespondents.respondent_id,SurveyRespondents.survey_id;

I really do not know if this is helpful or not, but since you seem to be looking for ideas.