How do I sum up values resulting from calculated fields in a query?

[kitoned]

I am trying to make an end of term report for a school. Briefly this is where the problem is:



Exams query: e.g [ENG],[MATH],[SCIE] [Aggregate],[Total Aggregate],[Division]

Aggregate is derived from the range of marks got e.g 75-100= Aggregate 1
70-74 = Aggregate 2
65-69 = Aggregate 3
Asumming MK ENG=95,MK MATH=72, MK SCIE=65

Aggregate ENG: IIf([MK ENG] Between 0 And 39,"9",IIf([MK ENG] Between 40 And 44,"8",IIf([MK ENG] Between 45 And 49,"7",IIf([MK ENG] Between 50 And 54,"6",IIf([MK ENG] Between 55 And 59,"5",IIf([MK ENG] Between 60 And 64,"4",IIf([MK ENG] Between 65 And 69,"3",IIf([MK ENG] Between 70 And 74,"2",IIf([MK ENG] Between 75 And 100,"1","Missed Exam")))))))))

Ihave tested the above and it works great!.MK ENG=95 give Aggregate1,MK MATH=72 give Aggregate2 and MK SCIE=65 gives Agregate3

The problem:
May some one help me with the formula for determining the [total aggregates] from the above results so that I can latter on determine the division. Below is how I tried it:
Total Aggregates:[Aggregate ENG]+[Aggregate MATH]+[Aggregate SCIE]. The result is 123 instead of "6"
OR
Is there any one with a better way of doing the above.
Thaks in advance.

[lfpm062010]

All you need to do is to convert your text result to a number like

Total Aggregates: CInt([Aggregate ENG])+CInt([Aggregate MATH])+CInt([Aggregate SCIE])

[kitoned]

All you need to do is to convert your text result to a number like

Total Aggregates: CInt([Aggregate ENG])+CInt([Aggregate MATH])+CInt([Aggregate SCIE])

Thanks my dear. This really worked. I wish to be as good as you are in Access. Do you have a community? If so I should Join the same.

kitoned

[June7]

Don't enclose the aggregate values within quote marks to begin with and CInt should not be necessary. CInt should error if value has alpha characters, like "Missed Exam".

Switch() function is an option for nested IIf.

Aggregate ENG: Switch([MK ENG]<40,9, [MK ENG]<45,8, [MK ENG]<50,7, [MK ENG]<55,6, [MK ENG]<60,5, [MK ENG]<65,4, [MK ENG]<70,3, [MK ENG]<75,2, [MK ENG]<=100,1)

Arithmetic with Null returns Null. If any of the values are Null there will not be a sum. Handle Null.

Total Aggregates: Nz([Aggregate ENG],0)+Nz([Aggregate MATH],0)+Nz([Aggregate SCIE],0)

[kitoned]

Don't enclose the aggregate values within quote marks to begin with and CInt should not be necessary. CInt should error if value has alpha characters, like "Missed Exam".

Switch() function is an option for nested IIf.

Aggregate ENG: Switch([MK ENG]<40,9, [MK ENG]<45,8, [MK ENG]<50,7, [MK ENG]<55,6, [MK ENG]<60,5, [MK ENG]<65,4, [MK ENG]<70,3, [MK ENG]<75,2, [MK ENG]<=100,1)

Arithmetic with Null returns Null. If any of the values are Null there will not be a sum. Handle Null.

Total Aggregates: Nz([Aggregate ENG],0)+Nz([Aggregate MATH],0)+Nz([Aggregate SCIE],0)

This is actually better than the method before and I have tested it. It works without any error.
But please I am still having a problem with the positions. This should be based on the total marks obtained from all subjects. See below:
[Total marks] after "Sort Descending" in my query:
Edward=590
John=460
Peter=400

Please help me to assign positions 1st , 2nd and 3rd in the same query.

[June7]

So you calculate each student score and want them to sort by the total score?

Did you apply sort order criteria to the calculated field?

Post the sql statement of the query.

[kitoned]

So you calculate each student score and want them to sort by the total score?

Did you apply sort order criteria to the calculated field?

Post the sql statement of the query.

This here on is my SQL:

SELECT [StudentExamsQ].[Student Name], [StudentExamsQ].[Total Score],[StudentExamsQ].[Position in class] FROM StudentExamsQ ORDER BY [Total Score] DESC , [Position in class];

As you can see from aboveTotal score is sorted Descending and I have no problem with that.
Total Score: NZ([MK ENG],0)+NZ([MK MTC],0)+NZ([MK SCIE],0)+NZ([MK SST],0)+NZ([MK RE],0)+NZ([MK LUG],0)+NZ([MK LIT1],0+NZ([MK LIT2],0))

The position in class is sorted Ascending (no need to add ASC). My problem is : How do I asign the positions to my Total scores before sorting.

Here is part of the table I have :
Student Name / Total score / Position in class / Expected Position in class
Edward / 590 / ? / 1
John / 460 / ? / 2
Peter/ 400 / ? / 3

[June7]

Ranking in query is complex. Review http://allenbrowne.com/ranking.html