Cumulative totals in Access 2003
I need to be able to calculate cumulative totals on groups of data in a query. Specifically I have a list of repair order numbers with associated part numbers required, dates the order was placed and the amount of the part needed for the repair. I need to be able to calculate a running cumulative total of amount required for each group of part numbers sorted by earliest order date to lates order date of the part number. So for part number ABC I need the cumulative running total of amount required, for part number DEF I need the cumulative running total of amount required, etc. with each group of parts sorted by date the order was placed.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 24, 2011 1:01 PM
UPDATED:
July 12, 2011 4:02 PM
Answer Wiki:
Can you use a report to display this information? Reports has a Running Sum property for an object. Or use could use VB in the report to keep a running sum.
If you want it in a query, you could create a function in a module. You would have your running total dimmed in the global area so it isn't reset each time the query calls the function. You would need to keep track of when control grouping changes so you can clear the running sum. One of the columns in the query would reference the function like:
RunningTotal: GetRunningTotal(PartNo, Amount)
Also, make sure your query has the proper sorting and grouping.
Your function might look like this:
<pre>
Option Compare Database
Option Explicit
Dim RunningTotal as double, PrevPartNo as String
Function GetRunningTotal (PartNo As String, Amount As Double) As Double
If IsNull(PrevPartNo) or PrevPartNo <> PartNo Then
PrevpartNo = PartNo 'PartNo changed or first time through the function
RunningTotal = Amount 'start RunningTotal over for new or first part
Else
RunningTotal = RunnintTotal + Amount
End if
GetRunningTotal = RunningTotal 'Pass back the current value of running total
End Function
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
I did see the running total in the reports, however I need to be able to compare the part required quantity by date to an “on hand” amount to find the exact date that I will run out of parts. I will give your function a try and give feedback. Thank you for your help!
Randym,
Thanks for you answer. I was just wondering how do you determine the order that the records are passed through the function? It doesn’t seem to be done by the sorting as far as I can see. I have it sorted the way that I would want to pass the records through, but it is not using that order and I don’t see the logic. Any help?
Thanks much.
Katie