Running sum in Access query with Group By

Question

I can't get a running sum to work in an Access query. I have a pipesystem, where I'm trying to summarize the flow Q, through the pipenetwork. I've been trying to do a running sum based on a group_by ID_downstream and a DSum on Q_total. However I keep getting errors or wrong input.

The desired output, is that I can see the flow accumulated through the network as shown in the table and picture.

Answer 1

You have several options. One, however, won't do, and that is a recursive query using SQL only; Access can't be fooled and will claim about a circular reference. Your only chance is to create a query resolving a limited number of levels only, say, 8 or 10.

But you can cover the recursive call in a domain aggregate function like DLookup. This is, however, very slow as DLookup calling the query will run for each and every record. For more than some dozens of records this will most likely be unacceptable.

The fastest way, for an unlimited number of levels, I've found, is to create a lookup function which walks the tree for each record. This can output either the level of the record or a compound key build by the key of the record and all keys above.

As the lookup function will use the same recordset for every call, you can make it static, and (for JET/ACE) you can improve further by using Seek to locate the records.

Here's an example which will give you an idea:

Function RecursiveLookup(ByVal lngID As Long) As String

  Static dbs      As Database
  Static tbl      As TableDef
  Static rst      As Recordset

  Dim lngLevel    As Long
  Dim strAccount  As String

  If dbs Is Nothing Then
    ' For testing only.
    ' Replace with OpenDatabase of backend database file.
    Set dbs = CurrentDb()
    Set tbl = dbs.TableDefs("tblAccount")
    Set rst = dbs.OpenRecordset(tbl.Name, dbOpenTable)
  End If

  With rst
    .Index = "PrimaryKey"
    While lngID > 0
      .Seek "=", lngID
      If Not .NoMatch Then
        lngLevel = lngLevel + 1
        lngID = !MasterAccountFK.Value
        If lngID > 0 Then
          strAccount = str(!AccountID) & strAccount
        End If
      Else
        lngID = 0
      End If
    Wend
    ' Leave recordset open.
    ' .Close
  End With

'  Don't terminate static objects.
'  Set rst = Nothing
'  Set tbl = Nothing
'  Set dbs = Nothing

'  Alternative expression for returning the level.
'  (Adjust vartype of return value of function.) '  RecursiveLookup = lngLevel ' As Long
  RecursiveLookup = strAccount

End Function

This assumes a table with a primary key ID and a foreign (master) key pointing to the parent record - and a top level record (not used) with a visible key (AccountID) of 0.

Now your tree will be nicely shown almost instantaneously using a query like this, where Account will be the visible compound key:

  SELECT
    *, RecursiveLookup([ID]) AS Account
  FROM
    tblAccount
  WHERE
    AccountID > 0
  ORDER BY
    RecursiveLookup([ID]);