Re: correlated subquery in the crosstab

where you have scores in the crosstab you could have, with additional where
clauses for exam and whatever, something along the lines of:
Result: [score] & " " & (SELECT Count(student.score)
FROM student, student AS student_1
WHERE (((student.score)>=[student_1.score]))
GROUP BY student.ID;)

which could give you score and ranking in each column.

<hamidrjafari@xxxxxxxxx> wrote in message
news:1160212653.657254.51730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Duane

thanx for the comments,
It might be better to tell you what I want to do. Maybe you can direct
me in the correct pathe and help me save time on not tackling it such
way that does not produce the results expected.

I have students, courses, exam groups containing exams of courses,
classes and educational years. It is actually an educational reporting
system. What I want is a crosstab query that can output scores of
students on exams(courses selected for examination) AND
another one that can rank scores of students for the same exams. I have
done the first one easily, but not the second one. The first query
results in
-----------------------------------------------------------------------------------
exam1 exam2 exam3 ... exam n
st1 12 11.75 20
st2 18 17 16
st3 14.5 8 9.25
.
.
.
stm
-----------------------------------------------------------------------------------
Here is its query:

TRANSFORM First(tblScore.score) AS FirstOfscore
SELECT tblStudent.lastname & " - " & tblStudent.firstname AS fullName,
Avg(tblScore.score) AS studentAve
FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON
tblEduYear.eduYearID = tblExamGrp.eduYear) INNER JOIN (tblCourse INNER
JOIN tblExam ON tblCourse.courseID = tblExam.course) ON
tblExamGrp.examGrpID = tblExam.examGrp) INNER JOIN tblScore ON
tblExam.examID = tblScore.exam) ON tblStudent.studentID =
tblScore.student
GROUP BY tblStudent.lastname & " - " & tblStudent.firstname
PIVOT tblCourse.name;

the second query is expected to produce ranks.
For example (just for three students and three exams of the above
example)
st1 is 3rd in exam1 (column1),
st2 is 1st in exam1 (column1),
st3 is 2nd in exam1 (column1),
...
st1 is 2nd in exam2 (column2),
st2is 1st in exam2 (column2),
st3is 3rd in exam2 (column2),
...

and the query I am dueling is:

TRANSFORM (SELECT count(*) FROM (SELECT tblScore.score FROM tblScore
WHERE (tblScore.exam=[tblExamAlias].[examID]) GROUP BY tblScore.score)
WHERE score >=[tblScore].[scoreAlias]) AS rankInEduYear
SELECT tblStudent.lastname & " - " & tblStudent.firstname AS fullName,
Avg(tblScoreAlias.score) AS studentAve
FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON
tblEduYear.eduYearID=tblExamGrp.eduYear) INNER JOIN (tblCourse INNER
JOIN tblExam AS tblExamAlias ON tblCourse.courseID=tblExamAlias.course)
ON tblExamGrp.examGrpID=tblExamAlias.examGrp) INNER JOIN tblScore AS
tblScoreAlias ON tblExamAlias.examID=tblScoreAlias.exam) ON
tblStudent.studentID=tblScoreAlias.student
GROUP BY tblStudent.lastname & " - " & tblStudent.firstname
PIVOT tblCourse.name;

I found how to rank and used stored queries that accepted parameters
for ranking scores of just one exam (a single column), but it downed at
me it would be much brilliant if I coud do it using a crosstab that can
supply parameter info (the columns) automatically to itself.

Of course the next thing I am thinking about is its efficiency:
considering that for every cell of the same column there would be
redundant retrival of distinct scores of that same column (exam) for
the sake of ranking.

Any help and insight is appreciated.
Sorry to make it long.

Duane Hookom wrote:
Crosstabs don't handle subqueries very well. You often must resort to
domain
aggregate functions such as DCount() or DSum() or other.
Row averages are the same as an average in a totals query. To average
across
records, use a report with averages in footers or headers.

--
Duane Hookom
MS Access MVP

<hamidrjafari@xxxxxxxxx> wrote in message
news:1160178684.967748.265160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi all,

After searching the net for a complete day or two, I have concluded
that the crosstab query in access has much more capablities than
expected. What I am looking for is the possiblities of putting sql
correlated subqueries in the body of the crosstab query say the
TRANSFORM clause.

And besides does anyone bother to know the dynamics of rows and columns
of the crosstab. for example is it possible to have row and column
averages.

have fun,
Hamid

Duane Hookom wrote:
Crosstabs don't handle subqueries very well. You often must resort to
domain
aggregate functions such as DCount() or DSum() or other.
Row averages are the same as an average in a totals query. To average
across
records, use a report with averages in footers or headers.

--
Duane Hookom
MS Access MVP

<hamidrjafari@xxxxxxxxx> wrote in message
news:1160178684.967748.265160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi all,

After searching the net for a complete day or two, I have concluded
that the crosstab query in access has much more capablities than
expected. What I am looking for is the possiblities of putting sql
correlated subqueries in the body of the crosstab query say the
TRANSFORM clause.

And besides does anyone bother to know the dynamics of rows and columns
of the crosstab. for example is it possible to have row and column
averages.

have fun,
Hamid





.

Relevant Pages

  • RE: Report to Include All Possible Values from CrossTab Query
    ... I don't see the results of the cartesian query in your crosstab as suggested ... exam, medical exam, etc for police recruits). ... and tblOutcomes with has Pass and Fail in the Outcomes ...
    (microsoft.public.access.reports)
  • Re: Not First, Not Last in Crosstab Query
    ... make a query that gives you all students and all tests. ... Crosstab --> RowHeading ...
    (microsoft.public.access.queries)
  • Re: cross tab query outer join
    ... And make your crosstab query based on that query you would have just saved. ... your tblExam is probably best labeled StudentsExams since it probably hold a many to many relation between the pk of tables Students and Exams. ... Stduent ID, Exam1, Exam 2, Exam Count ...
    (microsoft.public.access.queries)
  • RE: Calculations based on counts in a crosstab
    ... How do you handle students who transfer into a cohort and are not there ... Where you have your crosstab query, ... Select CohortNo, Term, Countas Students ...
    (microsoft.public.access.queries)
  • RE: Calculations based on counts in a crosstab
    ... Cohort # is defined as the term they start. ... Or just start with the select query? ... Where you have your crosstab query, ... Select CohortNo, Term, Countas Students ...
    (microsoft.public.access.queries)
Loading