Arthur,
The first thing that comes to mind in order to achieve this in a query isa
user-defined function in VBA. It is alot easier to do it in a report,
though, without a single line of code. So if that does the job for you, all
you need to do is create a report on the table (or even better, on a query
on the table, so you can sort, add criteria etc.), group on PersonID, and
add a second textbox for Credits and set its running sum property to
OverGroup.
If you definitely need it in a query, then do the following:
Open a new module and paste the following code:
Dim vTotalCredit As Long
Dim vPerID As Long
Function Total_Credits(P_ID As Long, Cred As Long)
If P_ID <> vPerID Then
vTotalCredit = 0
vPerID = P_ID
End If
vTotalCredit = vTotalCredit + Cred)
Total_Credits = vTotalCredit
Enf Function
Function Open_Query
vTotalCredit = 0
DoCmd.OpenQuery "YourQueryName" 'Change to the actual query name
End Function
Then open your query in design view, and add a calculated field as follows:
Total: Total_Credits([PersonID],[Credits])
(assuming that these are indeed the field names, or change as required).
Note: To be on the safe side, you should not open the query directly; it
should be opened through the code in Function Open_Query, just to make sure
the vTotalCredit variable is zeroed every time, otherwise you may get wrong
results if the PersonID value in the last record of a run is the same as in
the first record of the next one. You can use a macro with a RunCode action
to run the code, or a Comman button on a form, in which you will paste the
two lines of code in the function.
HTH,
Nikos
