使用 Group By 在 Access 查询中运行 sum [英] 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.
由于查找功能将为每次调用使用相同的记录集,您可以将其设为静态,并且(对于 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 查询中运行 sum的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!