doing bin ranges in a query?

[pietlinden]

consider the standard grading query: given a numeric grade, dblGrade,
I can do this to return the corresponding letter grade:

IIF(dblGrade>=90,"A",
IIF(dblGrade>=80,"B",
IIF(dblGrade>=70,"C",
IIF(dblGrade>=60,"D","F"))))

I am trying to do something similar with a non-equijoin query. I am
working on a database for cancer research, so what I'm grading are
toxicities of different types, and each type has a different bin range
for A-F.
To make things a little more "fun", for some toxicities a low value is
good (like Cholesterol) while for others a low value is bad (like
White Blood Cell counts). What I was hoping to do was to put the range
cutoffs in a table, something like this:

tblToxRanges(ToxName, CutOff, SortAscDesc)

so that I can create a table of values and a query that will function
like a VLookup in Excel. That way I can create the table once,
specify the Toxicity Name to get its bin values, and return a toxicity
grade. And without using a zillion IF statements.

how do I do it? When I do a non-equijoin, I always miss the data at
the edges of the bin range and then it knocks out the min or max value
every time.

thanks,
Pieter

 

[Dale Fye]

Pieter,

Rather than your format: tblToxRanges(ToxName, CutOff, SortAscDesc)
what I would recommend is:

tblToxRanges
ToxName (text)
ToxRngStart (Numeric, probably single, may be double)
ToxRngEnd (Numeric, datatype same as ToxRngStart)
ToxRngGrade (Text)

With data that looks like (these are totally made up):

ToxName ToxRngStart ToxRngEnd ToxRngGrade
Tox1 0.0 25.0 Great
Tox1 25.0 30.1 Good
Tox1 30.1 45.0 Fair
Tox1 45.0 75.0 Poor
Tox1 75.0 999 Terrible
Tox2 0.0 190.0 Good
Tox2 190.0 300.0 Poor
Tox2 300.0 999 Terrible
Tox3 0.0 50.0 Terrible
Tox3 50.0 75.0 Good
Tox3 75.0 999 Excellent

Then, in you query, you might have:

SELECT PatientID, ToxName, ToxRngGrade
FROM tblPatientTox INNER JOIN tblToxRanges
ON tblPatientTox.ToxName = tblToxRanges.ToxName
WHERE tblPatientTox.ToxValue >= tblToxRanges.ToxRngStart
AND tblPatientTox.ToxValue < tblToxRanges.ToxRngEnd

HTH
Dale

 

[Marshall Barton]

consider the standard grading query: given a numeric grade, dblGrade,
I can do this to return the corresponding letter grade:

IIF(dblGrade>=90,"A",
IIF(dblGrade>=80,"B",
IIF(dblGrade>=70,"C",
IIF(dblGrade>=60,"D","F"))))

I am trying to do something similar with a non-equijoin query. I am
working on a database for cancer research, so what I'm grading are
toxicities of different types, and each type has a different bin range
for A-F.
To make things a little more "fun", for some toxicities a low value is
good (like Cholesterol) while for others a low value is bad (like
White Blood Cell counts). What I was hoping to do was to put the range
cutoffs in a table, something like this:

tblToxRanges(ToxName, CutOff, SortAscDesc)

so that I can create a table of values and a query that will function
like a VLookup in Excel. That way I can create the table once,
specify the Toxicity Name to get its bin values, and return a toxicity
grade. And without using a zillion IF statements.

how do I do it? When I do a non-equijoin, I always miss the data at
the edges of the bin range and then it knocks out the min or max value
every time.

I don't see how a non-equi join can help here, but there may
be some tricky way that I'm missing.

I think tblToxRanges should look like:
ToxName Text (primary key)
CutOff Double
ToxGrade Text

I don't see why your proposed SortAscDesc field is needed.
All you require is the correct cut off and grade text. E.g.
some sample records might be:

Cholesterol 0 Eat eggs fried in butter
Cholesterol 100 Keep it up
Cholesterol 180 Eat more oatmeal
Cholesterol 230 Prescribe medication and dieting
Cholesterol 310 Put on life support
White Cell 0 Order bubble
White Cell 20 Schedule transplant
White Cell 60 looking good
White Cell 167 Look for cause

With that kind of arrangement, I think you can use a
subquery along the lines of this air code:

SELECT P.id, p.patiemtname, ...,
V.ToxName, V.toxValue,
(SELECT Top 1 R.ToxGrade
FROM tblToxRanges As R
WHERE R.ToxName = V.ToxName
And R.CutOff <= V.toxValue
ORDER BY R.CutOff) As Grade
FROM patients As P INNER JOIN toxvalues As V
ON P.id = V.patientid

 

[Dale Fye]

Marsh,

I like your ToxGrades much better than mine. I started out along the same
track as you, but was afraid someone would think I was being a bit too
flippant. ;-)