Urgent need!! (1 Viewer)

[dawmah]

Hi,
I have a query table that is from 2 tables namely student details and student exams( this has raw subject marks eg. English paper 1, paper 2 etc) In this query table there are calculated fields for average subject marks for each student. I need an extra field with an expression to calculate the sum of only the highest 7 subject marks. This is easy in excel and the formula is like this : " =+SUM(LARGE(E2:S2,{1,2,3,4,5,6,7}))".
Is there a similar expression in access? this would and prevent me using excel and importing into access, which is tedious.
Please help

 

[jzwp22]

Access is not a spreadsheet, so data is handled totally differently from Excel.

An appropriate structure for the exam table would look like this


tblExams
-pkExamID primary key, autonumber
-fkStudentID foreign key to student details table
-fkSubjectID foreign key to a table that holds all subjects
-txtExam (the name of the exam or paper etc.)
-score


In Access, you would not store the average or the sum of the scores but calculate them via a query like this:

SELECT fkStudentID, fkSubjectID, Sum(score), Avg(score)
FROM tblExams
GROUP BY fkStudentID, fkSubjectID

 

[dawmah]

Thanks! for that and this is the result of the query
Scores S_Name S_Surname S_Class English Maths Mary Mo 1A 53.5 29
This is similar to the table that we use for capturing marks though. A
And the challenge is to order the marks and get the top 7 subjects. Eg if the above student did sat for nine subjects then I d want a query that picks top7 and get their sum. How do I do that.

 

[jzwp22]

Scores S_Name S_Surname S_Class English Maths Mary Mo 1A 53.5 29

Each subject should be listed as a separate record in the query I suggested.

You might take a look at Allen Browne's site regarding how to obtain the TOP n Records for a group.

 

[tariqbablu]

I have the same problem,,,????

CAN ANYONE HELP ME PLEASE???

I am making a STUDENT RESULT DATABASE in Access for different classes. Some class have 7 subjects and some 10 and so on,,,
The rule is as follows:

(1).. Passing Marks 33 (Each subject requires 33 marks out of 100 )

(2)..Total 12 Marks will be given as Grace Marks in any limit of subjects if
needed. But in one subject can be given maximum 8 grace marks out of
12 Grace Marks.
(( That means if any subject < 33 to >24 will get grace marks ))

(3)..Maximum 2 subjects will be allowed for re-exam if <24 OR needed
more than the Grace Marks limit.

In query I made expression for Total of all subjects and Count of total Re-exam subject, working fine as below:

Subjects..........Sub1....Sub2....Sub3....Total.....Tot_Grace.....Re-Exam.....Result
Obt. Marks......25........26.......28........79
Grace Mrks......8..........7.........5.......................20.................??................???

In Re-Exam I gave the following code:

sup: IIf([Quran]<[Gracelimit],1,0)+IIf([Hadees]<[Gracelimit],1,0)+IIf([Urdu]<[Gracelimit],1,0)

But, I don’t know how to do for the GRACE MARKS. Please See 4th student. He needs GRACE MARKS 20 which are more than 12. So after 12 he will be eligible for re-exam that means:

Sub1 = Grace Marks (8)
Sub2 = Re-exam
Sub3 = Re-exam

But if we start to give him Grace Marks like:

Sub3 = Grace Marks (5)
Sub2 = Grace Marks (7)
Sub1 = Re-exam

The only thing I need to start giving the GRACE MARKS from the Subject needed minimum Grace Marks.

As you see above example if we start from the first subject, he needs in (2) subjects re-exam and when we started to give the Grace Marks from the Subject requires minimum Grace Marks effected the result and he needs only one subject re-exam.

Please, help me anyone getting this result, if possible through the MODULE.
Thanks
(Tariq)