使用 Group By 在 Access 查询中运行总和 [英] Running sum in Access query with Group By

问题描述

我无法在 Access 查询中获得运行总和.我有一个管道系统，我试图在其中总结通过管道网络的流量 Q.我一直在尝试根据 group_by ID_downstream 和 Q_total 上的 DSum 计算运行总和.但是我不断收到错误或错误的输入.

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.

推荐答案

您有多种选择.然而，一个不行，那是一个仅使用 SQL 的递归查询；Access 不会被愚弄，并且会声称是循环引用.您唯一的机会是创建一个仅解析有限数量级别的查询，例如 8 或 10.

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.

但是您可以在DLookup 之类的域聚合函数中覆盖递归调用.然而，这是非常慢的，因为 DLookup 调用查询将为每条记录运行.对于超过几十条记录，这很可能是不可接受的.

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.

由于查找函数会为每次调用使用相同的记录集，您可以将其设为静态，并且(对于 JET/ACE)您可以使用 Seek 进一步改进定位记录.

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

这假设一个表具有主键 ID 和指向父记录的外(主)键 - 以及具有可见键(AccountID)的顶级记录(未使用)) 的 0.

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.

现在，您的树将使用这样的查询几乎立即显示出来，其中 Account 将是可见的复合键:

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]);

这篇关于使用 Group By 在 Access 查询中运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！