dynamic crosstab query

[iccsi]

I have follwing data to create crosstab query and column heading is
Grade.

Student ID GRADE

1 A
2 B
3 A
4 D
5 C


I wanted to create a crosstab and use the query after.

The issue is sometimes the crosstab does not return "C" and "D"
column, since there is no C and D grade.

Can I have a dynamic crosstab query for differnt results?

For example, if there is A and B grade than the result will be
Student ID, Total, A, B. If there is C in the grade returns Student
ID, A, B, C.


Because another query needs run the query, is it possible to have a
dynamic crosstab query?


Your help is great appreciated,

 

[KARL DEWEY]

The IN function for the PIVOT part will do it.
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 IN("A", "B", "C", "D");

 

[John Spencer]

Instead of worrying about dynamic, define the column names

In the SQL view
TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT Grades("A","B","C","D","E")

IF you are in the Query design view (the query grid) then double click on a
blank area in the top section and in the Query Properties: Column Headings
Type in the grade values.

When you do the above there will be a column for each value you enter in the
column headings and only the column headings you enter will appear. So if you
have F as a grade (but rarely, you should include that grade also in the headings.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County