J
Jt3mitch1
I've been racking my brains to figure out the best way to approach my problem.
I have test results I need to organize:
ID date labnumber(unique) result
1 10/01/01 1 15.0
1 10/01/01 2 7.0
1 11/01/01 3 0.0
1 11/01/01 4 0.0
1 11/01/01 5 7.0
2 12/01/02 6 15.0
2 12/01/02 7 1.0
2 01/01/03 8 1.0
2 01/01/03 9 2.5
I'm trying to use a crosstab query to convert data to:
ID date rank1 rank2
rank3
1 10/01/01 7.0 15.0
1 11/01/01 0.0 0.0
7.0
2 12/01/02 1.0 15.0
2 01/01/03 1.0 2.5
I'll have up to 40 results for each ID. Too many different results in the
database to use results as column headings. I need to figure out how to rank
the results and have this rank as the column heading.
I'm bad at VBA, so after checking through newsgroups, I saw a post by Steve
Dassin that looked promising. This is a general idea of what I'm trying to
adjust to meet my needs:
TRANSFORM Max(result) AS data
SELECT ID, date,
DCount("*","query1","(" & "[ID]='"&ID&"'" &")" & "AND" &"(" &
"[result]<"&result &
"OR" & "(" & "[result]="&result & "AND" & "[labnumber]<"&labnumber & ")" &
")" ) + 1 AS alias,
Count(data) AS cnt,
FROM query1
GROUP BY ID, date,
PIVOT
DCount("*","query1","(" & "[ID]='"&ID&"'" &")" & "AND" &"(" &
"[result]<"&result &
"OR" & "(" & "[result]="&result & "AND" & "[labnumber]<"&labnumber & ")" &
")" ) + 1;
Thanks for any assistance,
JT
I have test results I need to organize:
ID date labnumber(unique) result
1 10/01/01 1 15.0
1 10/01/01 2 7.0
1 11/01/01 3 0.0
1 11/01/01 4 0.0
1 11/01/01 5 7.0
2 12/01/02 6 15.0
2 12/01/02 7 1.0
2 01/01/03 8 1.0
2 01/01/03 9 2.5
I'm trying to use a crosstab query to convert data to:
ID date rank1 rank2
rank3
1 10/01/01 7.0 15.0
1 11/01/01 0.0 0.0
7.0
2 12/01/02 1.0 15.0
2 01/01/03 1.0 2.5
I'll have up to 40 results for each ID. Too many different results in the
database to use results as column headings. I need to figure out how to rank
the results and have this rank as the column heading.
I'm bad at VBA, so after checking through newsgroups, I saw a post by Steve
Dassin that looked promising. This is a general idea of what I'm trying to
adjust to meet my needs:
TRANSFORM Max(result) AS data
SELECT ID, date,
DCount("*","query1","(" & "[ID]='"&ID&"'" &")" & "AND" &"(" &
"[result]<"&result &
"OR" & "(" & "[result]="&result & "AND" & "[labnumber]<"&labnumber & ")" &
")" ) + 1 AS alias,
Count(data) AS cnt,
FROM query1
GROUP BY ID, date,
PIVOT
DCount("*","query1","(" & "[ID]='"&ID&"'" &")" & "AND" &"(" &
"[result]<"&result &
"OR" & "(" & "[result]="&result & "AND" & "[labnumber]<"&labnumber & ")" &
")" ) + 1;
Thanks for any assistance,
JT