ACCESS 2016 - running totals by group

[ClassyD]

I have 30 records pulling back for running total. The query is returning running total balances. However when I put the following code in its pulling back all records twice. I want the code to put 15 records with group 1/CompanyType pullback running total and 15 records with group 2 pull back running total for the same company. I want the records to keep running total based on the field CompanyType. When I add company type to this query no records pull back

Event Procedure

Dim strSQL As String
strSQl = "Select * from Company where companyid = " GetCompanyid() " & "Order by companyid, employeeid asc "
Dim rs As Dao.Recordset
Set rs = CurrentDB.OpenRecordset(strSQl)
Dim prvRecValue As Double
prvRecValue = 0#
Dim calcUsed As Double
Dim calcAdded As Double
Dim firstRec as Boolean
firstRec = True

If Not (rs.EOF And rs.BOF) Then


rs.MoveFirst
Do Until rs.EOF = True
rs.Edit
If firstRec - True Then
prvRecValue = 0
firstRec = False
End If
If IsNull(rs!Added) = True Then
calcAdded = 0
Else
calcAdded = rs!Added
End If
If IsNull(rs!Used) = True Then
calcUsed = 0
Else
calcUsed = rs!Used
End If
rs!Bal = prvRecValue + calcAdded - calcUsed
prvRecValue = rs!Bal
rs.Update
rs.MoveNext
Loop
Else
MsgBox "No records in recordset"
End If
rs.Close
Set rs = Nothing
End Sub

[June7]

Saving aggregate data is usually bad idea. Can do this on report without VBA by using textbox RunningSum property.

Your SQL statement concatenation is wrong.

strSQl = "Select * from Company where companyid = " & GetCompanyid() & " Order by companyid, employeeid;"

Should post code segments between CODE tags to retain indentation and readability.

[ClassyD]

The code is pulling the correct data, but duplicating the data twice instead of totaling the data by contact type. What is the code and how to do a group by the data by ContactType which is a String.


strSQl = "Select * from Company where companyid = " GetCompanyid() " & " Order by companyid, employeeid asc "

I'm getting this:
Company id Companytype Employeeid Add Used Balance
65 PlanA 300 10 10
65 PlanA 301 6 4
65 PlanB 302 5 9

Should Be Group by PlanB:
Company id Companytype Employeeid Add Used Balance
65 PlanA 300 10 10
65 PlanA 301 6 4
65 PlanB 302 5 5

[June7]

Using your 3 record example, the following works for me:

Dim rs As DAO.Recordset
Dim strType As String
Dim dblBal As Double
Set rs = CurrentDb.OpenRecordset("Select * from Company where companyid = 65 Order by companyType, employeeid;")
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    strType = rs!companyType
    Do Until rs.EOF
        If strType <> rs!companyType Then
            dblBal = 0
            strType = rs!companyType
        End If
        dblBal = dblBal + Nz(rs!Add, 0) - Nz(rs!Used, 0)
        rs.Edit
        rs!Balance = dblBal
        rs.Update
        rs.MoveNext
    Loop
Else
    MsgBox "No records in recordset"
End If
rs.Close
Set rs = Nothing

Still recommend you just do this calculation in report and not save to table.