crosstab query

[inungh]

I have following data and would like to have a statistic report and
tried to use one crosstab query. It seems that I need to use 2
crosstab queries, since I need 2 values columns.


Student ID, Exam Date, Results,
1 1/1/2009 PASS
1 1/1/2009 FAILED
1 1/2/2009 FAILED
2 1/1/2009 FAILED
2 1/3/2009 PASS

I would like to have report list following

Student, 01/01 01/02 01/03
1 50% 100%
2 0 % 100%

I can use one crosstab on results and onther one for Exam date,
because I need to count how many PASS and FAILED for students.

I tried to use a sub query in the corsstab, but MS Access does not
let me to have GROUP BY in the subquery.


I am looking for a solution using one crosstab query.

Is it possible?

Your information is great appreciated,

 

[KARL DEWEY]

The first query builds a file with vevery combination of ID and ExamDate.
inungh_1 --
SELECT inungh.[Exam Date], inungh_1.[Student ID]
FROM inungh, inungh AS inungh_1
GROUP BY inungh.[Exam Date], inungh_1.[Student ID]
ORDER BY inungh.[Exam Date], inungh_1.[Student ID];

The second assigns a value to Pass, Failed, or Absent.
inungh_2 --
SELECT inungh_1.[Exam Date], inungh_1.[Student ID], IIf([Results] Is
Null,0,IIf([Results]="PASS",1,2)) AS Expr1
FROM inungh_1 LEFT JOIN inungh ON (inungh_1.[Exam Date] = inungh.[Exam
Date]) AND (inungh_1.[Student ID] = inungh.[Student ID])
ORDER BY inungh_1.[Exam Date], inungh_1.[Student ID];

The crosstab gives the Pass Percentage or shows absences.
TRANSFORM
IIf(Sum([Expr1])=0,"--",IIf(Sum([Expr1])=Count([Expr1]),0,(Sum(IIf([Expr1]=2,1,0))/Count([Expr1]))*100)) AS Expr2
SELECT inungh_2.[Student ID]
FROM inungh_2
GROUP BY inungh_2.[Student ID]
PIVOT Format([Exam Date],"Short Date");

 

[inungh]

The first query builds a file with vevery combination of ID and ExamDate.
    inungh_1 --
SELECT inungh.[Exam Date], inungh_1.[Student ID]
FROM inungh, inungh AS inungh_1
GROUP BY inungh.[Exam Date], inungh_1.[Student ID]
ORDER BY inungh.[Exam Date], inungh_1.[Student ID];

The second assigns a value to Pass, Failed, or Absent.
    inungh_2 --
SELECT inungh_1.[Exam Date], inungh_1.[Student ID], IIf([Results] Is
Null,0,IIf([Results]="PASS",1,2)) AS Expr1
FROM inungh_1 LEFT JOIN inungh ON (inungh_1.[Exam Date] = inungh.[Exam
Date]) AND (inungh_1.[Student ID] = inungh.[Student ID])
ORDER BY inungh_1.[Exam Date], inungh_1.[Student ID];

The crosstab gives the Pass Percentage or shows absences.
TRANSFORM
IIf(Sum([Expr1])=0,"--",IIf(Sum([Expr1])=Count([Expr1]),0,(Sum(IIf([Expr1]=­2,1,0))/Count([Expr1]))*100)) AS Expr2
SELECT inungh_2.[Student ID]
FROM inungh_2
GROUP BY inungh_2.[Student ID]
PIVOT Format([Exam Date],"Short Date");

I have following data and would like to have a statistic report and
tried to use one crosstab query. It seems that I need to use 2
crosstab queries, since I need 2 values columns.

Student ID, Exam Date, Results,
   1              1/1/2009       PASS
   1               1/1/2009     FAILED
   1               1/2/2009     FAILED
   2               1/1/2009     FAILED
   2               1/3/2009     PASS

I would like to have report list following

Student, 01/01     01/02      01/03
   1           50%    100%
   2           0 %                    100%

I can use one crosstab on results and onther one for Exam date,
because I need to count how many PASS and FAILED for students.

I  tried to use a sub query in the corsstab, but MS Access does not
let me to have GROUP BY in the subquery.

I am looking for a solution using one crosstab query.

Is it possible?

Your information is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks millions,