Reset Running Total in Access query

[d1novak]

I am using a query to Calculate the RunningSum of Hours for a Time Clock. As you can see the below formula works and even recognizes when day number is lower than previous record (Indicates New Week)and results in [TotalHours]. However I need the RunningSum to reset to [TotalHours] rather than continue on.

RunningSum: IIf((SELECT ([DayNumber]) FROM [tblHours] AS [tblHours_1] WHERE (([tblHours_1].[ID]= [tblHours].[ID]-1)))>[DayNumber],[TotalHours],(SELECT Sum([TotalHours]) FROM [tblHours] AS [tblHours_1] WHERE [tblHours_1].[ID] <= [tblHours].[ID]))

Any Ideas?

[tt]ID TotalHours DayNumber RunningSum
[tt]55 10.5 2 10.5[/tt]
[tt]56 8.75 3 19.25[/tt]
[tt]57 9.5 4 28.75[/tt]
[tt]58 9.5 5 38.25[/tt]
[tt]59 9.5 6 47.75[/tt]
[tt]60 9.5 2 9.5[/tt]
[tt]61 9.5 3 66.75[/tt]
[tt]62 9.5 4 76.25[/tt]
[tt]63 8.75 5 85[/tt]
[tt]64 8.75 6 93.75[/tt]
[tt]65 8.75 4 8.75[/tt]
[tt]66 8.75 5 111.25[/tt]
[tt]67 8.75 6 120[/tt]
[tt]68 12 1 12[/tt]
[tt]69 12 2 144[/tt]
[tt]70 8 2 152[/tt]
[tt]71 8 3 160[/tt]
[tt]72 8 4 168[/tt]
[tt]73 8 5 176[/tt]
[tt]74 8 6 184[/tt]
[tt]75 10 2 10[/tt]
[tt]76 10 3 204[/tt]
[tt]77 8 4 212[/tt]
[tt]78 10 5 222[/tt]
[tt]79 8 6 230[/tt]
[tt]80 10 4 10[/tt]
[tt]81 8 5 248[/tt]
[tt]82 10 ` 6 258[/tt]

Thanks,
Dan

 

[jsteph]

If you're using this for a visual output, you could use an Access Report, which has the RunningSum property, which can be set OverGroup or OverAll, the group could be the week.

If this query is a source for some procedure or something, then possibly doing this all in that procedure using nested loops would be easier to code and more reliable.
--Jim