string crosstab query

[inungh]

I have following data and would like to have a crosstab query


StudentID,Exam Date, Pass, Total Exam
1 1/1/2009 3 5
1 1/5/2009 2
1 1/10/2009 0 1
1 1/20/2009

The report will be

Student ID, 1/1/2009, 1/5/2009, 1/10/2009, 1/20/2009
1 3 of 5 0 of 2 0 of 1

If pass is null then the student absent

I need help that is it possible to have crosstab using string type
value?

Your help is great appreciated,

 

[John Spencer MVP]

IF you have only one record per student per date then you can use

First([Pass] & " of " & [Total Exam]) as the source for the value.

The SQL would look something like the following

TRANSFORM First([Pass] + " of " + [Total Exam])
SELECT [StudentID]
FROM TheTable
GROUP BY [StudentID]
PIVOT [Exam Date]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County