correlated subquery in the crosstab

[hamidrjafari]

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]

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.

 

[hamidrjafari]

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.

 

[David F Cox]

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.

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.

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.

 

[hamidrjafari]

Hi David,

Unfortunately Jet complains that it does not recognize student_1 as a
valid field name. I guess aliases are not supported in TRANSFORM
clause.

Thanks for the discussion. I have to resort to the other ways. By the
way I have bumped into another glitch:

Is it possible to reference an alias two or more levels deep inside
correlated subqueries?
Have a nice day.

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.

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.

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

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

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

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]

Like I stated earlier, crosstabs don't like subqueries. You might be about
to use the very slow DCount() in place of the subquery.

--
Duane Hookom
MS Access MVP

Hi David,

Unfortunately Jet complains that it does not recognize student_1 as a
valid field name. I guess aliases are not supported in TRANSFORM
clause.

Thanks for the discussion. I have to resort to the other ways. By the
way I have bumped into another glitch:

Is it possible to reference an alias two or more levels deep inside
correlated subqueries?
Have a nice day.

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.

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

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

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

 

[David F Cox]

Your SQL does not recognise student_1 because you do not have a field called
student_1.

I wrote "something along the lines of" because I just do not have time to go
through your SQL and get all of the fieldnames and syntax exactly right. I
am not on here to do your work for you. I am trying to give you some
assistance over the hurdles, to help advance your understanding. I hoped
that you would go through it and understand it and adapt it and be abale to
do it for yourself next time. It is leaving a disaster to happen if you are
using code that you do not understand.

Also, this forum is not just for you. The reason I posted at all is that
there are a lot of people that do not realise that you can fudge a crosstab
query to give several values in the grid, though it is a dangerous activity
in the wrong hands.

Good luck in your endeavours, and I apologise for not being clearer.

David F. Cox

Hi David,

Unfortunately Jet complains that it does not recognize student_1 as a
valid field name. I guess aliases are not supported in TRANSFORM
clause.

Thanks for the discussion. I have to resort to the other ways. By the
way I have bumped into another glitch:

Is it possible to reference an alias two or more levels deep inside
correlated subqueries?
Have a nice day.

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.

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

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

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

 

[hamidrjafari]

Hi David,
Sorry for the misunderstanding. I really substituted your code with
appropriate field names of mine and ended up with what I said. I
thought I'd better show that I have done as you stated but I was so
explicit. Sorry.

Daune, I have actually left the crosstab way, though I dreamed of how
an elegant way it could be. I have now my classic solution in front of
me. But still need help from you experts.

PARAMETERS idOfEduYear Long, idOfExam Long;
SELECT tblScoreAlias.student, tblScoreAlias.scoreID,
tblScoreAlias.score,
------------------------------------------------------------------------------------------------\
(SELECT COUNT(*)
FROM (SELECT tblScore.score
FROM (tblStudent INNER JOIN (tblClass INNER JOIN
tblRegistration ON tblClass.classID = tblRegistration.class) ON
tblStudent.studentID = tblRegistration.student) INNER JOIN tblScore ON
tblStudent.studentID = tblScore.student
WHERE (((tblRegistration.class)=***tblRegistrationAlias.class***) AND
((tblScore.exam)=[idOfExam]))
GROUP BY tblScore.score)
WHERE score >= tblScoreAlias.score) AS rankInClass,
-------------------------------------------------------------------------------------------------/
tblRegistrationAlias.eduYear, tblScoreAlias.exam
FROM (tblStudent INNER JOIN tblRegistration AS
***tblRegistrationAlias*** ON tblStudent.studentID =
tblRegistrationAlias.student) INNER JOIN tblScore AS tblScoreAlias ON
tblStudent.studentID = tblScoreAlias.student
WHERE (((tblRegistrationAlias.eduYear)=[idOfEduYear]) AND
((tblScoreAlias.exam)=[idOfExam]))
ORDER BY tblScoreAlias.score DESC;

The correlated subquery that I have highlighted does the ranking. I
want it to rank for all classes. I have not introduced
"tblRegistrationAlias.class" as a parameter. All I intended was to get
values from the outer query and I have made an alias for it. But on
query execution access opens the box to get its value from user and
uses that for all processing. Is it possible to use an alias two or
more levels deep into subqueries. Or something else is wrong?

I have marked the alias.

Agian thanks for your patience and care.

Hamid

Your SQL does not recognise student_1 because you do not have a field called
student_1.

I wrote "something along the lines of" because I just do not have time to go
through your SQL and get all of the fieldnames and syntax exactly right. I
am not on here to do your work for you. I am trying to give you some
assistance over the hurdles, to help advance your understanding. I hoped
that you would go through it and understand it and adapt it and be abale to
do it for yourself next time. It is leaving a disaster to happen if you are
using code that you do not understand.

Also, this forum is not just for you. The reason I posted at all is that
there are a lot of people that do not realise that you can fudge a crosstab
query to give several values in the grid, though it is a dangerous activity
in the wrong hands.

Good luck in your endeavours, and I apologise for not being clearer.

David F. Cox

Hi David,

Unfortunately Jet complains that it does not recognize student_1 as a
valid field name. I guess aliases are not supported in TRANSFORM
clause.

Thanks for the discussion. I have to resort to the other ways. By the
way I have bumped into another glitch:

Is it possible to reference an alias two or more levels deep inside
correlated subqueries?
Have a nice day.

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.

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

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

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

 

[hamidrjafari]

Hi,

I solved my problem using stored queries to act as subqueries. It is
working.

Thanx everyone.

Hi David,
Sorry for the misunderstanding. I really substituted your code with
appropriate field names of mine and ended up with what I said. I
thought I'd better show that I have done as you stated but I was so
explicit. Sorry.

Daune, I have actually left the crosstab way, though I dreamed of how
an elegant way it could be. I have now my classic solution in front of
me. But still need help from you experts.

PARAMETERS idOfEduYear Long, idOfExam Long;
SELECT tblScoreAlias.student, tblScoreAlias.scoreID,
tblScoreAlias.score,
------------------------------------------------------------------------------------------------\
(SELECT COUNT(*)
FROM (SELECT tblScore.score
FROM (tblStudent INNER JOIN (tblClass INNER JOIN
tblRegistration ON tblClass.classID = tblRegistration.class) ON
tblStudent.studentID = tblRegistration.student) INNER JOIN tblScore ON
tblStudent.studentID = tblScore.student
WHERE (((tblRegistration.class)=***tblRegistrationAlias.class***) AND
((tblScore.exam)=[idOfExam]))
GROUP BY tblScore.score)
WHERE score >= tblScoreAlias.score) AS rankInClass,
-------------------------------------------------------------------------------------------------/
tblRegistrationAlias.eduYear, tblScoreAlias.exam
FROM (tblStudent INNER JOIN tblRegistration AS
***tblRegistrationAlias*** ON tblStudent.studentID =
tblRegistrationAlias.student) INNER JOIN tblScore AS tblScoreAlias ON
tblStudent.studentID = tblScoreAlias.student
WHERE (((tblRegistrationAlias.eduYear)=[idOfEduYear]) AND
((tblScoreAlias.exam)=[idOfExam]))
ORDER BY tblScoreAlias.score DESC;

The correlated subquery that I have highlighted does the ranking. I
want it to rank for all classes. I have not introduced
"tblRegistrationAlias.class" as a parameter. All I intended was to get
values from the outer query and I have made an alias for it. But on
query execution access opens the box to get its value from user and
uses that for all processing. Is it possible to use an alias two or
more levels deep into subqueries. Or something else is wrong?

I have marked the alias.

Agian thanks for your patience and care.

Hamid

Your SQL does not recognise student_1 because you do not have a field called
student_1.

I wrote "something along the lines of" because I just do not have time to go
through your SQL and get all of the fieldnames and syntax exactly right. I
am not on here to do your work for you. I am trying to give you some
assistance over the hurdles, to help advance your understanding. I hoped
that you would go through it and understand it and adapt it and be abale to
do it for yourself next time. It is leaving a disaster to happen if you are
using code that you do not understand.

Also, this forum is not just for you. The reason I posted at all is that
there are a lot of people that do not realise that you can fudge a crosstab
query to give several values in the grid, though it is a dangerous activity
in the wrong hands.

Good luck in your endeavours, and I apologise for not being clearer.

David F. Cox

Hi David,

Unfortunately Jet complains that it does not recognize student_1 as a
valid field name. I guess aliases are not supported in TRANSFORM
clause.

Thanks for the discussion. I have to resort to the other ways. By the
way I have bumped into another glitch:

Is it possible to reference an alias two or more levels deep inside
correlated subqueries?
Have a nice day.

David F Cox wrote:
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.

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

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

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