Crosstab twist.

[pietlinden]

I am attempting to summarize data that looks like this (here's the
query.. I would give the the table design but the table design is non-
normal, so it would be worse!):

I am basically looking for the worst reactions ("Toxicities") to a
drug and what those reactions are attributed to. The problem I am
having is that I can get the Maximum Toxicity per Toxicity per Patient
just fine. But when I include the Attribution Code (which tells me
what probably caused the Toxic event), I can get only the Max Grade
for each ([Attribution Code], [Toxicity]) pair, which is not what I
want.


SELECT [Q].[Record#], [Q].[Pt Number], [Q].[Phase of Treatment],
[Q].Toxicity, [Q].Grade, [Q].[Attribution Code]
FROM [Q]
WHERE ((([Q].Grade) Is Not Null))
ORDER BY [Q].Toxicity;

from this, I create a totals query to return the maximum Grade per
Toxicity per Patient, which works fine. Where I'm puzzled is how to
do this but then keep the 'Attribution Code'. Do I have to dump a
filtered set to a table and then join back to get the [Attribution
code]? for just the records with the Maximum grade?

"Toxicity"="side effect" (think drug commercials on TV)

What I have been asked to come up with is a crosstab query.
Column 1=Toxicity
Column 2=Grade
then the subsequent columns are How strongly related the toxic event
is to the drug treatment.

how would I go about doing this in SQL?

I'm at a loss! Any clues would be a big help! Thanks!

Pieter