专家坐堂-[已解决]怎样把表1中最后一次生成的余额加到表2中Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [已解决]怎样把表1中最后一次生成的余额加到表2中  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=176992)

--  作者：z_dafei
--  发布时间：2022/5/6 0:26:00
--  [已解决]怎样把表1中最后一次生成的余额加到表2中

此主题相关图片如下：2.png

做一个流水账，其他功能都实现了，就想把明细表中的最后的余额按照账户列加载到账户表
下载信息  [文件大小：   下载次数： ]
点击浏览该文件:明细账.rar

的余额中。就是在账户表中做一个各账户余额的统计。明细表中，对应的账户列最后一个余额，是该账户的最终余额。始终没整明白。为啥过不去呢
此主题相关图片如下：1.png

Select Case e.DataCol.Name
Case "账户","借","贷"
Dim drs As List(of DataRow)
Dim dt As DataRow = DataTables("各种账户的名称").Find("余额 = \'" & e.OldValue & "\'")
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [账户] = \'" & e.DataRow("账户") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)",Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)",Filter)
dt = DataTables("各种账户的名称").AddNew()
dr("余额") = Val1 - Val2
dt("余额") = dr("余额")
Next
If e.DataCol.Name = "账户" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [账户] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户借") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)",Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)",Filter)
dt = DataTables("各种账户的名称").AddNew()

dr("余额") = Val1 - Val2
dt("余额") = dr("余额")
Next
End If
End Select

[此贴子已经被作者于2022/5/6 12:16:41编辑过]

--  作者：有点蓝
--  发布时间：2022/5/6 8:41:00
--

Select Case e.DataCol.Name
Case "账户", "借", "贷"
Dim drs As List(Of DataRow)
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [账户] = \'" & e.DataRow("账户") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dt = DataTables("各种账户的名称").AddNew()
dr("余额") = Val1 - Val2
Next
Dim dt As DataRow = DataTables("各种账户的名称").Find("账户= \'" & e.DataRow("账户") & "\'")
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.DataRow("账户")
End If
Dim dr2 As DataRow = e.DataTable.find("账户= \'" & e.DataRow("账户") & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
If e.DataCol.Name = "账户" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [账户] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户借") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dt = DataTables("各种账户的名称").AddNew()

dr("余额") = Val1 - Val2
dt("余额") = dr("余额")
Next
dt = DataTables("各种账户的名称").Find("账户= \'" & e.OldValue & "\'")
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.OldValue
End If
dr2 = e.DataTable.find("账户= \'" & e.OldValue & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
End If

End Select

--  作者：z_dafei
--  发布时间：2022/5/6 11:00:00
--

大神能帮我分析一下吗？看着太乱了，这个也不好使，我也不知道问题在哪
Select Case e.DataCol.Name
Case "账户", "借", "贷"
Dim drs As List(Of DataRow)
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [账户] = \'" & e.DataRow("账户") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dt = DataTables("各种账户的名称").AddNew() ’这个dt是不是得先dim一下
dr("余额") = Val1 - Val2
Next
Dim dt As DataRow = DataTables("各种账户的名称").Find("账户= \'" & e.DataRow("账户") & "\'") ’ 还是说需要把这个挪到前面
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.DataRow("账户")
End If
Dim dr2 As DataRow = e.DataTable.find("账户= \'" & e.DataRow("账户") & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
If e.DataCol.Name = "账户" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [账户] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户借") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dt = DataTables("各种账户的名称").AddNew()

dr("余额") = Val1 - Val2
dt("余额") = dr("余额") ’这三句怎么理解？
Next
dt = DataTables("各种账户的名称").Find("账户= \'" & e.OldValue & "\'")
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.OldValue
End If
dr2 = e.DataTable.find("账户= \'" & e.OldValue & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
End If

End Select

--  作者：有点蓝
--  发布时间：2022/5/6 11:26:00
--

Select Case e.DataCol.Name
Case "账户", "借", "贷"
Dim drs As List(Of DataRow)
Dim Filter As String
Filter = "[_SortKey] >= " & e.DataRow("_SortKey") & " And [账户] = \'" & e.DataRow("账户") & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dr("余额") = Val1 - Val2
Next
Dim dt As DataRow = DataTables("各种账户的名称").Find("账户= \'" & e.DataRow("账户") & "\'")
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.DataRow("账户")
End If
Dim dr2 As DataRow = e.DataTable.find("账户= \'" & e.DataRow("账户") & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
If e.DataCol.Name = "账户" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [账户] = \'" & e.OldValue & "\'"
drs = e.DataTable.Select(Filter)
For Each dr As DataRow In drs
Filter = "[_SortKey] <= " & dr("_SortKey") & " And [账户] = \'" & dr("账户借") & "\'"
Dim Val1 As Double = e.DataTable.Compute("Sum(借)", Filter)
Dim Val2 As Double = e.DataTable.Compute("Sum(贷)", Filter)
dr("余额") = Val1 - Val2
Next
dt = DataTables("各种账户的名称").Find("账户= \'" & e.OldValue & "\'")
If dt Is Nothing Then
dt = DataTables("各种账户的名称").AddNew()
dt("账户") = e.OldValue
End If
dr2 = e.DataTable.find("账户= \'" & e.OldValue & "\'", "[_SortKey] desc")
dt("余额") = dr2("余额")
End If

End Select

--  作者：z_dafei
--  发布时间：2022/5/6 12:02:00
--

神呀，这个为啥变成这样了？但是数值出来了，再帮我看看呗。
然后我把账户表的余额列的数值给删除后，再重新写入代码，就没反应了

[此贴子已经被作者于2022/5/6 12:07:15编辑过]

--  作者：有点蓝
--  发布时间：2022/5/6 12:04:00
--
变成啥样？

--  作者：z_dafei
--  发布时间：2022/5/6 12:08:00
--
变成这样了
删除之后就没有反映了

[此贴子已经被作者于2022/5/6 12:09:14编辑过]

--  作者：z_dafei
--  发布时间：2022/5/6 12:14:00
--
好使了，但是如果明细表中有删除行，账户表中的数据不变。谢谢大神，我再研究研究。哈哈

--  作者：有点蓝
--  发布时间：2022/5/6 13:33:00
--
删除行到datarowdeleteing事件处理

--  作者：z_dafei
--  发布时间：2022/5/6 20:25:00
--
感谢，基本功能实现了，之后慢慢研究。