Creating Running Sum with Grouping Query
Looks like your GROUP BY subclause says to group by Force_no but also the yyyy and m datepart values for Subs_Dt as separate items. [Also since you are returning "Force_no AS FN", you need to refer to it as 'FN' and not 'Force_no' in the other subclauses of the statement. Same is true for your partial-date references as well in the SELECT subclause] So the query will return the grouping not by Force_no (should be FN) and some partial date but instead by Force_no (shoukd be FN), the year, and then the month.
The fix would be to group by a concatenation of the year and month and to change the references to the fields to be what you renamed them as in your SELECT subclause using "...AS...", for example:
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 FN, AYear & "-" & AMonth
ORDER BY AYear, AMonth;
So now you have only 2 GROUP BY parameters instead of three and the grouped results won't be done by FN, then year, then month, but by FN and year-month. [As for the "-" in GROUP BY subclause, you can use any character you want as the separator; I just picked "-" since it is so typical.]
The DSum function in the SELECT subclause may also need some rework; also, possibly you will need to return [Subs_Dt] in the clause as well so DSum() has it to work with. Also check to be sure that reference to [Subs_Dt] is being recognized OK without using the "table!field" form of reference. I dunno if you need it given I don't know your whole table-field definition and so can't say if there would be a naming conflict someplace.
If you are still having trouble, try starting over and building this query piece by piece, one field, one condition, one subclause, at a time until you get to where it breaks for you. That'll be the "threshold of failure" you are looking for that should point you further in the right direction.
I hope this works for you. Let me know how it goes!
×