使用分组依据在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的递归查询.访问不可上当，并且会要求提供循环引用.您唯一的机会是创建一个仅解决有限数量的级别(例如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.

由于查找函数将为每个调用使用相同的记录集，因此可以使其变为 static ，并且(对于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]);

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