Running Sum with Grouping

[rahulndeepti]

I have a table GIS_Subs with following fields:
Force_No ( Foreign Key)
Subs_Dt
Block Yr
Subs_Amt (number)
Running_Total
I wish to update Running Total for each record based on previous pay sorted in Ascending.
I am able to make a running sum but then it clubs all PersonNo of same date like:
SELECT GIS_Subs.Force_No AS FN, DatePart("yyyy",GIS_Subs!Subs_Dt) AS AYear, DatePart("m",GIS_Subs!Subs_Dt) AS AMonth, DSum("Subs_Amt","GIS_Subs","DatePart('m', [Subs_Dt])<=" & [AMonth] & " And DatePart('yyyy', [Subs_Dt])<=" & [AYear] & "") AS RunTot
FROM GIS_Subs
GROUP BY GIS_Subs.Force_No, DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt)
ORDER BY DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt);COLOR]
How can I calculate it for each Person seperately?

 

[DCrake]

Simple Software Solutions

Without giving it much thought and only responding to the last line in your thread
How can I calculate it for each Person seperately?

Why dont you introduce a new group by field in your query which is person based, therefore you will get a total for each person at the end of the group

CodeMaster:

 

[rahulndeepti]

Sadly it does'nt work!!!

Sorry! Tried that already!!!

 

[Cosmos75]

How about this?

SELECT Force_No AS FN, Subs_Dt, Subs_Amt,
       (SELECT Sum(a.[Subs_Amt])
        FROM GIS_Subs AS a
        WHERE a.[Force_No] = GIS_Subs.[Force_No]
        AND a.[Subs_Dt] <= GIS_Subs.[Subs_Dt]) AS dblRunningTotal
FROM GIS_Subs
ORDER BY Force_No, Subs_Dt;

You will have to be carefull about having two entries for FN that have the same date.

You may find the following article helpful - Running Sum / Total / Count / Average in a query using a correlated subquery