Rolling Sum in Access

[JoeC_Glenmark]

Hey Guys,

Am a total newbie, so this one should be real easy to solve for you folks.

Have a table/query result
Sr.NO Name Amt.Tot Amt.Needed
1 Mark 100 24
2 Mark 100 80
3 Tom 150 12
4 Tom 150 45
5 Joe 50 23

Need to add a field which is Amount.LEft so that the table looks like this

Sr.NO Name Amt.Tot Amt.Needed Amt.Left
1 Mark 100 24 76
2 Mark 100 80 -4
3 Tom 150 12 138
4 Tom 150 45 93
5 Joe 50 23 27

The last field is Amt.Tot - Amt.Needed and this should roll over and subtract for all transactions of Mark, then restart again for Tom and then again for Joe. Let me know if there is any confusion on that.


Please let me know how to.

Regards,
Joe

 

[pr2-eugin]

See if this query will help..

SELECT [Name], AmtTot, Sum(AmtNeeded) AS SumOfAmtNeeded, [AmtTot]-[SumOfAmtNeeded] AS AmtLeft
FROM tblQty
GROUP BY [Name], AmtTot;

NOTE: Not tested, but in Theory should work...

On a totally different note, you need to change field names 'Name' is a reserved keyword..

 

[JoeC_Glenmark]

Ok, it worked, thanks a million!!

 

[pr2-eugin]

I would love to help, but what do you mean by "does not work"? Did it give you wrong result? No result? Error in Query? What does not work?

Also it might help if we see the query..

EDIT: Okay glad you have it working..

 

[JoeC_Glenmark]

I guess i made a mistake while filling it out..

Works like a charm!

Thanks,
Joe

 

[JoeC_Glenmark]

Have an additional question.

Suppose I have an additional field - say date

Is it possible to find out the amount left for each person on each date?

Regards,
Joe