string crosstab query

  • Thread starter Thread starter inungh
  • Start date Start date
I

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,
 
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
 
Back
Top