Adding SubjectScoreRank Column to student report sheet for each student

[joehwhell]

Hi! I am pretty new to access and I am working on a college report sheet generating system.
I have a report built on a query that displays the result of each student as shown below(The Rank column and the ClassAvg are what i want to achieve)

Subject	Score	Rank	ClassAvg
English Language	90	1	74
Mathematics	70	3	88
Social Studies	88	2	74
Civic Education	77	4	67
French Language	78	3	65
Writing Skills	88	1	79
Basic Science	89	1	80

I want to insert two columns as above that display ranks(Rank) based on the score of a particular student in the class and the Class average(ClassAvg) based on the average score of the class in each subject.
For example is there are four students taking 7 subjects, the Rank column should display the rank of the student (between 1-4) in descending order of scores in each subject and the average of each subject based on the clas
Please I seriously need your help on this one.


Thanks in advance

[June7]

Assigning a rank within each subject won't be easy. For a start review http://allenbrowne.com/ranking.html. Possibly a VBA custom function will be required for your situation.

For the average, do an aggregate query that groups by subject and uses aggregate Avg() function then include that query in another query to join to data. The aggregate query will have to include the same filter criteria as the final query. Alternatively, use DAvg() domain aggregate function in query or in textboxes. Each expression will need the same filter criteria.

[joehwhell]

Assigning a rank within each subject won't be easy. For a start review http://allenbrowne.com/ranking.html. Possibly a VBA custom function will be required for your situation.

For the average, do an aggregate query that groups by subject and uses aggregate Avg() function then include that query in another query to join to data. The aggregate query will have to include the same filter criteria as the final query. Alternatively, use DAvg() domain aggregate function in query or in textboxes. Each expression will need the same filter criteria.

Thanks for the prompt response June7. I am not yet vast in the application of VBA function(infact just going through books on VBA) so I tried the DAvg() function using the design view. This gave me the same average for all students in all subjects. Here is the SQL generated:

SELECT qryBasicOneFirstTermReport.PupilID, qryBasicOneFirstTermReport.SubjectName, DAvg("[Total]","[qryBasicOneFirstTermReport]") AS ClassAvg
FROM qryBasicOneFirstTermReport
GROUP BY qryBasicOneFirstTermReport.PupilID, qryBasicOneFirstTermReport.SubjectName, DAvg("[Total]","[qryBasicOneFirstTermReport]")
ORDER BY qryBasicOneFirstTermReport.SubjectName;

What seems to be wrong?
I appreciate your effort replying me.

[June7]

Use Avg() not DAvg(). And do not use PupilID in the query.

The query you show does not consider time period. Does this db have multiple years and semesters?

For DAvg() you would need WHERE CONDITION argument.

DAvg("[Total]", "[qryBasicOneFirstTermReport]", "SubjectName='" & [SubjectName] & "'")

Does that query have filter criteria for session?

Domain aggregate functions can cause slow performance in queries.

[isladogs]

Have a look at this thread
https://www.accessforums.net/showthr...858#post360858
Post 12 may be particularly useful

[June7]

And the Allen Browne article also offers TEMP table as an option. The table should be permanent, records are temporary.

[joehwhell]

Use Avg() not DAvg(). And do not use PupilID in the query.

The query you show does not consider time period. Does this db have multiple years and semesters?

For DAvg() you would need WHERE CONDITION argument.

DAvg("[Total]", "[qryBasicOneFirstTermReport]", "SubjectName='" & [SubjectName] & "'")

Does that query have filter criteria for session?

Domain aggregate functions can cause slow performance in queries.

Thanks June7 for your time. But i am not getting the result i want yet.

[June7]

What are you getting? Show your latest attempt.

[joehwhell]

What are you getting? Show your latest attempt.

Thanks June7.
After using the code below, I got a rank of each student in each subject. (Code and result shown below)


SELECT Q1.PupilID, Q1.Name, Q1.SubjectName, Q1.CA, Q1.Exam, Q1.Total, (Select Count(*) from qryBasicOneFirstTermReport
Where Total > Q1.Total
And SubjectName = Q1.SubjectName)+1 AS Rank
FROM qryBasicOneFirstTermReport AS Q1
ORDER BY Q1.PupilID, Q1.SubjectName, Q1.Total DESC;

Result.


How do i include the ranking in my report so that it shows the subject ranking for a student when his/her result is viewed. (i.e I want a ranking column in the report)

[June7]

Review the Allen Browne link again.