Cumulative total function behaving strangely

[kherm]

I just found this forum, so I hope I'm in the right place and someone can help me.

I am converting data for an educational institution and I need to supply a class rank (freshman, sophomore) based on the earned credits. The old database doesn't have the class rank, and the new database requires it.

I wrote the following function that is behaving strangely:
Function run_total(id, crdearned As Integer) As Integer
Static beg_credit As Integer
Static new_id As Integer
Static first_row As Integer
If first_row = 0 Then
new_id = id
first_row = 1
End If
If id = new_id Then
If crdearned > 0 Then beg_credit = beg_credit + crdearned
run_total = beg_credit
Else: beg_credit = 0
run_total = 0
If crdearned > 0 Then beg_credit = crdearned
run_total = beg_credit
new_id = id
End If
End Function

The function is called in the query field this way:
class: (run_total([load_id],[crdearned]))

In debug mode, the first id passes through perfectly. Halfway through the second id, the process hangs. If I click on the query tab to view the results, the debugger restarts and the id has reverted back to the first id. If I follow along for awhile, the data in final result shows the data using the first id, but the id field in the result increments properly. So I have a function that just won't work and I don't know why.

Thanks in advance!

Ken

 

[MStef]

Hello kherm!
Look at "DemoRunningSumA97.mdb"
I think it is what you need.
Open Form1.
(MStef alias Štef)

 

[kherm]

my running sum must start over many times

I took a look at the database, but I need more functionality. I need a running sum or cumulative total for each student, so the running sum must start over when the ID for the student changes.

My function seems to have the right logic, but fails to keep the id number for some reason.

Can any one see what might be causing that?

Thanks for your help!

Ken

 

[MStef]

Ok kherm!
Here it is a new example,
I think it is OK now.

 

[kherm]

Thank you!

MStef, thank you!

I am getting what I want now. Here's the kicker. My code works, too, but certainly not as efficiently as yours. When I look at the the output directly from running the query, the data is wrong.

However, if I make it an append query, the data goes into the table correctly. Apparently the "Excel sheet view" of the query is dynamic and changed as I scrolled.

I discovered this when I was preparing a table of the output for you to view since I couldn't upload sensitive data.

Your help and attention is much appreciated.

Ken