How to determine the sum of aggregates obtained in best 8 of 12 subjects done in end of term exams

[kitoned]

I am trying to develope school report making software but summing aggregates for 8 best done out of 12 subjects has become a problem. Part of my report is as below:
Subject Marks Aggregates
Math 90 1
Hist 75 3
Geo 30 9


Eng 85 2
A 60 4
B 55 5
C 70 3
D 55 5
E 82 2
F 67 4
G 30 9
H 31 9

May some one out there show me how to sum up the best 8 using the Query results. Remember the lower the aggregate the better the performance. The aggregates are generated from part of a query similar to the one below:
StdName MathAgg HistAgg GeoAgg EngAgg A.. B.. C.. D.. E.. F.. G .. H..
Konde Jim 1 3 9 2 4 5 3 5 2 4 9 9

Aggregate best 8 should give me: 1+2+2+3+3+4+4+5 =24. I have not shown how I employ the Immediate If on the marks scored to determine the aggregates.

HELP P'SE

[amrut]

You can use TOP 8 in the query used to produce the report card.
I used following tables -
tblCandidates - CandidateID(PK), Candidate(Text)
tblSubjects - SubjectID(PK), SubjectName(Text)
tblGrades -GradeID(PK), CandidateIDFK(FK),SubjectIDFK(FK),Aggr
SELECT tblCandidates.Candidate, tblSubjects.SubjectName, tblGrades.Aggr
FROM tblSubjects INNER JOIN (tblCandidates INNER JOIN tblGrades ON tblCandidates.CandidateID = tblGrades.CandidateIDFK) ON tblSubjects.SubjectID = tblGrades.SubjectIDFK
WHERE (((tblGrades.GradeID) In (SELECT TOP 8 tblGrades.GradeID
FROM tblGrades
WHERE (((tblGrades.CandidateIDFK)=[tblCandidates]![CandidateID]))
ORDER BY tblGrades.Aggr ASC)));

Create you query based on above.

[kitoned]

Thanks amrut. I tried the Top 8 method before but somehow it could not give me the sum of the selected 8 out of the 12 entries. Actually what you get is the best 8 done subjects with their corresponding Aggr but not the sum of Aggr for those 8. My intrest is seeing how I envetually get the sum of the top eight Aggr ASC. Besides in my query the Aggr are generated using the IIf function on the marks scored. So I do not employ the tblGRADES.

Can I get an alternative p'se.

[amrut]

What I have provided is an example to achieve it. The above query can be modified to give total of TOP 8 Aggr for the candidates -
SELECT tblCandidates.Candidate, Sum(tblGrades.Aggr) AS SumOfAggr
FROM tblSubjects INNER JOIN (tblCandidates INNER JOIN tblGrades ON tblCandidates.CandidateID = tblGrades.CandidateIDFK) ON tblSubjects.SubjectID = tblGrades.SubjectIDFK
WHERE (((tblGrades.GradeID) In (SELECT TOP 5 tblGrades.GradeID
FROM tblGrades
WHERE (((tblGrades.CandidateIDFK)=[tblCandidates]![CandidateID]))
ORDER BY tblGrades.Aggr ASC)))
GROUP BY tblCandidates.Candidate;
Instead of the tables I used, you can create a query to calculate the aggregates (using IIF fumction) and then use that query to create a query similar to above. The concept remains the same.

[kitoned]

My dear amrut thanks a lot for the help. But p'se do not forget that I am only a Novice. I have tried to create the tables in your example above but still I cannot come up with the result; Total for best 8 =x Aggr. I think I still need some bit of spoon feeding. I shall also keep trying. The attached image below is an example of my results sheet derived from a similar structure query. Note that the field "Sum best 2 Agg" should have results of ID1=12, ID2=13 and ID3=15

More help p,se.

[amrut]

See the attachment
Grades.accdb

[amrut]

Further, to have subjects as column headers, you need a CROSSTAB query. Try it using the query wizard

[kitoned]

Hi amrut, It appears I have only you in the whole world who can assist me. So please bear with me. Thanks for the info of 3/12/14. I think I am picking up the concept steadily. I have attached some Images and a sample of research database which I would like you to look at and see where I went wrong. The problem is that when a candidate gets similar marks in all my 4 sample subjects the summation of the grades fails. Zero to fourty gives grade 1 and fourty one to one hundred gives grade2. I have used SELECT TOP 2 which means that the sum of grades for one who has above 40 in two or more subjects should be 2. You will notice that Kazo who at one time got 70 in all 4 subject returns 4 as sum for top 2. May you please solve this for me.


Sorry my attachment failed to upload because of size ---900KB . Max should be 500KB OR I am wrong! I shall keep trying to upload it.

[amrut]

Did you see the attachment ? Post the SQL of your query and sample data as in the table. If possible, post a a screenshot of your table relationships.

[kitoned]

Hi amrut. I did follow the example in your attachment to set up work. I have tried to create some screen shots but the quality remains wanting. I hope they will be useful.




P'se let me know if you still need more info. Thanks.

[amrut]

A single query cannot produce Sum as well as the top subjects.You need to add SubjectIDFK to the Order by clause then only the query will pickup Top 2 records even when marks are equal. But then, you will not be able to decide the subjects on your own then when marks are equal. The query will pull results based on the sort order.

In the attached database, change the marks as wish for testing and create these queries -
qryTop2 -
SELECT tblCandidates.CandidateID, tblCandidates.Candidate, tblSubjects.SubjectName, tblGrades.Aggr
FROM tblSubjects INNER JOIN (tblCandidates INNER JOIN tblGrades ON tblCandidates.CandidateID = tblGrades.CandidateIDFK) ON tblSubjects.SubjectID = tblGrades.SubjectIDFK
WHERE (((tblGrades.GradeID) In (SELECT TOP 2 tblGrades.GradeID
FROM tblGrades
WHERE (((tblGrades.CandidateIDFK)=[tblCandidates]![CandidateID]))
ORDER BY tblGrades.Aggr ASC, tblGrades.SubjectIDFK ASC )));

qryTotalofTop2-
SELECT tblCandidates.CandidateID, tblCandidates.Candidate, Sum(tblGrades.Aggr) AS SumOfAggr
FROM tblSubjects INNER JOIN (tblCandidates INNER JOIN tblGrades ON tblCandidates.CandidateID = tblGrades.CandidateIDFK) ON tblSubjects.SubjectID = tblGrades.SubjectIDFK
WHERE (((tblGrades.GradeID) In (SELECT TOP 2 tblGrades.GradeID
FROM tblGrades
WHERE (((tblGrades.CandidateIDFK)=[CandidateID]))
ORDER BY tblGrades.Aggr ASC, tblGrades.SubjectIDFK ASC)))
GROUP BY tblCandidates.CandidateID, tblCandidates.Candidate;

qryCombined -
SELECT qryTop2.Candidate, qryTop2.SubjectName, qryTotalofTop2.SumOfAggr
FROM qryTop2 INNER JOIN qryTotalofTop2 ON qryTop2.CandidateID = qryTotalofTop2.CandidateID;



See the result and apply it in your case.