Calculating cumulative average for students on a SIS

Hello everybody.I am working on an SIS and have a problem with the cumulative average. I have the result_tbl which includes index, name, subj_code, mark, units, subj_wt, (which is the multiple of mark & units) and semester. I tried the example provided in the Northwind database sample query of running totals but unfortunately it doesn't work. The query calculates the total subj_wt for all of the student’s records not only those corresponding to the current and previous semesters. Any suggestions?

Build a report that groups records by student, semester. Do aggregate calcs in group footers.

If you mean that you want only the current and immediate previous semesters, as opposed to all semesters, then you just have to limit your query to the desired semesters.

If the semesters desired have codes {curr} and {prev}, then your where clause would be something like

Code:

WHERE (SEMESTER = {curr}) OR (SEMESTER = {prev})

If semesters have a year and month date like 1308 for fall 2013 and 1302 for spring 2013, it might look like this:

Code:

WHERE SEMESTER >= 1302

SUSIS0.zipHello June 7
Many thanks for your suggestion, but unfortunately it doesn’t work. I’ve created a report based on index and semester and was able to calculate the Class Average for each semester, but for the cumulative average, it seems that we have to put additional criteria in the Dsum function criteria clause. A sample mini database is attached. Best regards.

A lot of thanks Dal Jeanis. I tried the following expression, which should consider in the first semester only the sum of subj_wt of that semester and then adds this sum to the sum of the subj_wt of the second semester and so on. But surprisingly it starts with a sum that is calcualeted using an unknown formula.[QUOTE]Cumulative Subj_wt: DSum(" [subj_wt] ";"[Result_tbl]";"[Semester2]

Sorry, can you please explain in words exactly what you want the query to return? Are you trying to get, as/of the end of each semester, the cumulative average of all courses taken by the student up to and including that semester?

Can you explain what the result-table is?
What does "Index" represent - is that the student's primary key?
Do semester, Semester1 and semester2 all represent the same piece of information?
Is subj_wt just the mark times the number of units?

Don't try to do too much in one query. Break down your question into discrete units. In this case, I'd break this down into two items - First, Query1 will calculate the semester units and semester weights.

Code:

Query1
  SELECT 
     TR.Index,
     TR.Semester.
     Sum(Result_tbl.units) AS SemUnits, 
     Sum(Result_tbl.subj_wt) AS SemWeight, 
     ([SemWeight]/[SemUnits]) AS SemAvg
  FROM
     Result_tbl AS TR
  GROUP BY 
     TR.Index,
     TR.Semester;

Then query2 will join query1 to itself to calculate cumulative units and cumulative weights up to and including each semester.

Code:

Query2:
  SELECT 
     Q1.Index,
     Q1.Semester.
     Q1.SemUnits, 
     Q1.SemWeight, 
     Q1.SemAvg,
     Sum(Q2.SemUnits) AS CumUnits,
     Sum(Q2.SemWeight) AS CumWeight,
     ([CumWeight]/[CumUnits]) AS CumAvg
  FROM
     Query1 As Q1
     INNER JOIN
     Query1 As Q2
     ON Q1.Index = Q2.Index
  WHERE 
     Q1.Semester >= Q2.Semester
  GROUP BY 
     Q1.Index,
     Q1.Semester
  ORDER BY 
     Q1.Index,
     Q1.Semester;

Many thanks Dal Jeanis. Done! Problem Solved

You're welcome. I don't use Dsum() within a query if I can help it, because I have no idea whether it's efficient or not in any given case, and it's not immediately apparent what will be summed in any given complex query.

The key is to break down your aggregate query information request into discrete chunks hat are both meaningful and certain.

I'm fairly sure that the Query1 construction will efficiently calculate the semester averages, and that then Query2 will efficiently calculate the running cumulatives based on the results of Query1.

The other thing is that you could take the intermediate results of Query1 and create a useful crosstab query from it just as easily.

For a crosstab, I'd create a utility table like this, and put one record in the table for each column I wanted to see in the crosstab. A column can be a single semester or a range of semesters.

Code:

tblCross1
   CrossPK     PK Autonumber
   CrossTitle  Text     
   FirstSem    Number   
   LastSem     Number   

FirstSem   LastSem    CrossTitle 
  00         07       2008-09 Year & Prev 
  08         09       2009-10 Year  
  10         11       2010-11 Year
  12         12       2011 Fall
  13         13       2012  Spring
  14         14       2012 Fall
  15         15       2013  Spring 
  16         16       2013 Fall
  00         99       Cumulative

You can see by the dataq above that, to get the Crosstab titles to arrange themselves correctly, I used an extra space before the word "Spring", otherwise the column for "2012 Fall" sorts to the left of "2012 Spring".
Here's the crosstab query to show the student's step-by-step weighed averages

Code:

Query3:
   TRANSFORM Format((Sum(SemWeight)/Sum(SemUnits)),"0.00") AS SemAvg
   SELECT Q1.Index
   FROM Query1 AS Q1, tblCross AS C1
   WHERE ( Q1.Semester BETWEEN C1.FirstSem AND C1.LastSem)
   GROUP BY Q1.Index
   PIVOT C1.CrossTitle;

With the same SQL code and different data in tblCross, you could create columns of running cums or whatever else you'd like.
If you wanted to have three lines per student, one each for the Units, Weight and Average, then you can create a second table like this:

Code:

tblCross2
   CrossValue  Text   (Three Records, "Units", "Weight" and "Average")

And use a crosstab query that cross-joins to the new table and calculates a different value for each line like this:

Code:

Query4:
TRANSFORM Format(IIF(C2.CrossField="UNITS",Sum(SemUnits),IIF(C2.CrossField = "Weight",Sum(SemWeight),(Sum(SemWeight)/Sum(SemUnits)))),"0.00") AS TheValue
SELECT Q1.Index, C2.CrossField
FROM Query1 AS Q1, tblCross AS C1, tblCross2 AS C2
WHERE ( Q1.Semester BETWEEN C1.FirstSem AND C1.LastSem)
GROUP BY Q1.Index, C2.CrossField
ORDER BY Q1.Index, C2.CrossField DESC 
PIVOT C1.CrossTitle;

Or, you could also change Query3 so that it calculates a formatted

Code:

Query3B:
TRANSFORM IIF(Sum(SemWeight)>0,Format(Int(Sum(SemWeight)),"00000") & "  /  " & Format(Int(Sum(SemUnits)),"000") & "    =    " & Format((Sum(SemWeight)/Sum(SemUnits)),"00.00"),"") AS TheResult
SELECT Q1.Index
FROM Query1 AS Q1, tblCross AS C1
WHERE ( Q1.Semester BETWEEN C1.FirstSem AND C1.LastSem)
GROUP BY Q1.Index
PIVOT C1.CrossTitle;