two level cross tab query

[iccsi]

I have data like following:

Student ID Grade CLASSS Level
1 PASS PIANO A
1 FAILED GYM
B
2 PASS PIANO A
2 PASS COMPUTER C
2 FAILED GYM D
3 PASS SWIM A
4 FAILED COMPUTER B
5 PASS DANCE B


and would like to have cross tab query for grade and level at same
time.
there is no relationship between grade and level.


I would like to have my result as following

Student ID PASS FAILDE A B C D
1 1 1 1 1 0 0
2 2 0 1 0 1 0
3 1 0 1 0 0 0


Is it possible to have a query to get result or I need stroe in a
local temp table?

Your help is great appreciated,

 

[KARL DEWEY]

Try this --
TRANSFORM Count(iccsi.Grade) AS CountOfGrade
SELECT iccsi.[Student ID], Sum(IIf([Grade]="Pass",1,0)) AS Pass,
Sum(IIf([Grade]="Failed",1,0)) AS Failed
FROM iccsi
GROUP BY iccsi.[Student ID]
PIVOT iccsi.Level;

 

[Wolfgang Kais]

Hello "iccsi".

I have data like following:

Student ID Grade CLASSS Level
1 PASS PIANO A
1 FAILED GYM
B
2 PASS PIANO A
2 PASS COMPUTER C
2 FAILED GYM D
3 PASS SWIM A
4 FAILED COMPUTER B
5 PASS DANCE B


and would like to have cross tab query for grade and level at same
time.
there is no relationship between grade and level.


I would like to have my result as following

Student ID PASS FAILDE A B C D
1 1 1 1 1 0 0
2 2 0 1 0 1 0
3 1 0 1 0 0 0


Is it possible to have a query to get result or I need stroe in a
local temp table?

Your help is great appreciated,

Assuming that the table is named Students, tra this:

TRANSFORM IIf(IsNull(Count(*)),0,Count(*)) AS LevelCount
SELECT Students.[Student ID], Sum(IIf([Grade]="PASS",1,0)) AS PASS,
Sum(IIf([Grade]="FAILED",1,0)) AS FAILED
FROM Students
GROUP BY Students.[Student ID]
PIVOT Students.Level In ("A","B","C","D");