Adding fields and showing incremental change

[DGersper]

I have a simple table

Student
Student_ID First_Name Last_Name Status
1 -------------James -------Smith--------- 5
2 -------------Jane--------- Doe -----------10
3 -------------Mark-------- Miller-------- 5
4 -------------Ty ------------Smith ---------3
5 -------------Shawn ------Williams ----3

And I want to create a query that adds the Status together (line by line) in descending order so that the final results show like:

Student Sum of Status Query
student_id First_Name Last_Name SumOfStatus
1 -------------James -------Smith ------- 26
2 -------------Jane ----------Doe ----------21
3 -------------Mark---------Miller--------11
4 -------------Ty -------------Smith --------6
5 -------------Shawn------- Williams--- 3

If I try something like:

SELECT [Student Query].Student_ID, [Student Query].First_Name, [Student Query].Last_Name, [Student Query].Status, Sum(student.status) AS SumOfStatus
FROM [Student Query]
GROUP BY [Student Query].Student_ID, [Student Query].First_Name, [Student Query].Last_Name, [Student Query].Status
ORDER BY Sum(student.status);

I simply get the status back out the same as the table.

Any ideas?

Thanks,
Dan

 

[pbaldy]

If you search on running sum you should find solutions. The totals query wouldn't work for you in this situation. The two common methods are a subquery or a DSum. Either one sums up records with a student ID greater than or equal to the current record's.