Cumulative totals in Access 2003

20 pts.
Tags:
Access 2003
Access 2003 forms
Access Database
Access query
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.
ASKED: January 24, 2011  1:01 PM
UPDATED: July 12, 2011  4:02 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Acmaintainer
    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!
    20 pointsBadges:
    report
  • RunningSum
    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
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following