crosstab query

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

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,
 
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");
 
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 said:
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.
Click to expand...
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
Click to expand...
I would like to have report list following
Click to expand...
Student, 01/01     01/02      01/03
   1           50%    100%
   2           0 %                    100%
Click to expand...
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.
Click to expand...
I  tried to use a sub query in the corsstab, but MS Access does not
let me to have GROUP BY in the subquery.
Click to expand...
I am looking for a solution using one crosstab query.
Click to expand...
Is it possible?
Click to expand...
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks millions,
 
Back
Top