formula to return a max value across fields?

[Stan]

i have a table of data that contains follow-up information for a large number
of patients. a patient may have multiple rows of data corresponding to
different follow-up dates after treatment. each row of data contains a
distinct date and associated grades of toxicity for that date of interest.

there are multiple types of toxicity that a patient can have (e.g. bladder
obstruction, bladder bleeding, incontinence). how could i write a command to
return the maximum grade of toxicity across these 3 columns (in this example,
if a patient has a grade 1 obstruction, a grade 2 bleed, and grade 2
incontinence i would like to return '2'')? is there a simple comparison
function within the "build" option?

additionally... i would like to identify the earliest date for which a grade
2 toxicity is seen. i know how to do this in a way that is complicated - i
can make a query that filters for grade 2 toxicity, and then write another
query to return the minimum date from that set of data. however in this
example that would require making 6 queries to get the dates associated with
each of these 3 toxicities. is there an easier way to return the earliest
date of any grade 2 bladder toxicity?

many thanks!

 

[MGFoster]

i have a table of data that contains follow-up information for a large number
of patients. a patient may have multiple rows of data corresponding to
different follow-up dates after treatment. each row of data contains a
distinct date and associated grades of toxicity for that date of interest.

there are multiple types of toxicity that a patient can have (e.g. bladder
obstruction, bladder bleeding, incontinence). how could i write a command to
return the maximum grade of toxicity across these 3 columns (in this example,
if a patient has a grade 1 obstruction, a grade 2 bleed, and grade 2
incontinence i would like to return '2'')? is there a simple comparison
function within the "build" option?

additionally... i would like to identify the earliest date for which a grade
2 toxicity is seen. i know how to do this in a way that is complicated - i
can make a query that filters for grade 2 toxicity, and then write another
query to return the minimum date from that set of data. however in this
example that would require making 6 queries to get the dates associated with
each of these 3 toxicities. is there an easier way to return the earliest
date of any grade 2 bladder toxicity?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should show the design of your table(s) 'cuz it isn't clear if you
have a table like this (columns):

patient_id
symptom_type - obstruction, bleed, or incontinence
toxicity_date - date the toxicity was tested
toxicity_grade

or and "Excel-type" table like this:

patient_id, toxicity_date,
bladder_obstruction, bladder_bleeding, incontinence
********** ( each holds toxicity grade ) ************

If you have the Excel-type table you should Normalize (redesign) the
table to be more like the first table example - it would make it easier
to develop a solution for your problem (and, besides, its good db
design).

If you can't change the design of the table you can produce a pseudo
Normalized version with a View (aka query) using the UNION like this:

Name it UninaryToxicities:

SELECT patient_id, toxicity_date, "Bladder Obstruction" As symptom_type,
bladder_obstruction As toxicity_grade
FROM DiseasePresentations
UNION ALL
SELECT patient_id, toxicity_date, "Bladder Bleeding", bladder_bleeding
UNION ALL
SELECT patient_id, toxicity_date, "Incontinence", incontinence

Now, using the UninaryToxicities query, the solution for the MAX
toxicity at earliest date is (relatively) simple:

SELECT patient_id, toxicity_date, toxicity_grade
FROM UninaryToxicities As UT
WHERE toxicity_date = (SELECT MIN(toxicity_date)
FROM UninaryToxicities
WHERE patient_id = UT.patient_id
AND toxicity_grade =
(SELECT MAX(toxicity_grade)
FROM UninaryToxicities
WHERE patient_id = UT.patient_id))


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScBJR4echKqOuFEgEQJOyACg0pFU0oYGsCNvg5+fkb6ukAp9tCQAoNLA
w5+ect4Bjm0pwM1cVO6P7O8Z
=HCv3
-----END PGP SIGNATURE-----

 

[Stan]

thanks for the reply. the data indeed are in excel type format. as each
patient has separate values of bladder toxicity the grades are expressed in
different columns.

is there a way to do this in design view as i am not SQL proficient? i don't
see a Union function and hope there is another way..

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A UNION can only be done in SQL view.

You can do an expression to get the Max value of the "Excel-type"
columns. Sorta like this (this should be all on one line. I broke it
into separate lines for clarity):

IIf(column1>column2 AND column1>column3 AND column1>column4,column1,
IIf(column2>column1 AND column2>column3 AND column2>column4,column2,
IIf(column3>column1 AND column3>column2 AND column3>column4,column3,
column4)))

Substitute your column names in the above. This will find the maximum
value of 4 columns. Add more imbedded IIf() functions for more columns.
Each IIf() function (each line in the above) is required for a
comparison of one column to all the other toxicity grade columns. The
above example only compares 4 columns. You'd have to add another AND
comparison for each new column. See why it's better to have a
Normalized table?! Read the Access Help article on the IIf() function
for more info. You can get directly to that article by:

1. Opening the Debug window (ctrl-G key combination)
2. Typing the function name.
3. Placing the cursor on that function name and hitting the F1 key.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScF+w4echKqOuFEgEQI9vwCggZ2Vvce4CvDfvSEXCmLtMSYilVQAn1T/
KtoSoMvw0ldrHCQLK0YAz9dL
=uwfU
-----END PGP SIGNATURE-----