主题：跨表统计及判断取值

 入下图，表c提供抵扣增值税，统计的表A数据，第一期没有问题

前置条件，每个项目都这样计算，用项目编码来区分

1、第二期：提供抵扣增值税怎么在统计的基数上+上一期（第一期）“本期抵扣剩余”

2、第三期：提供抵扣增值税怎么在统计的基数上+上一期（第二期）“本期抵扣剩余”

 

以此类推，可能会出现二十期或更多，这个用代码还是用表达式判断呢？请老师帮忙写一下。附件已上传。

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目1.table

知道怎么弄了，代码如下 

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
        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
        End If
 End  Select

 表C直接根据流水账设计

 

http://www.foxtable.com/webhelp/scr/1572.htm

 

http://www.foxtable.com/webhelp/scr/2136.htm

 

 统计表A的数据，参考

 

http://www.foxtable.com/webhelp/scr/1454.htm

 

老师，我补录数据，先录入第3期，在录入第2第，累计出现问题，如下图，（我要得效果是：第2期：累计计量金额：500000，第3期累计计量金额：2358151.5，累计扣款和累计支付也要这个效果，代码怎么修改了：

 

此主题相关图片如下：111.jpg

 

'累计计量?扣款?支付
Select Case  e.DataCol.Name
    Case  "项目编码","申请金额","本期_累计扣款","实际支付金额",    "企业所得税率","个人所得税基数","个人所得税率","管理费费率" , "手续费","安全保证金","质量保证金","应急保证金","资料保证金","fa piao金额","到账金额","成本fa piao","人工工资","其他费用","抵扣增值税","现金补税","预交增值税","应补增值税","管理费","应扣费用明细_小计","实际支付金额","本期_累计扣款","成本差额税率","成本差额税","扣税明细_小计","成本差额"
        Dim  dr As  DataRow
        Dim  mr As  DataRow = e.DataRow
        Dim  drs As  List(of DataRow)
        dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") &  " And [项目编码] = '" &  mr("项目编码") &  "'", "[_SortKey] Desc")
        If dr Is Nothing Then
            mr("累计_计量金额") = mr("申请金额")
            mr("累计_扣款金额") = mr("本期_累计扣款")
            mr("累计_支付金额") = mr("实际支付金额")  '("申请金额") - mr("本期_累计扣款")
            dr = mr
        End If
        drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") &  " And [项目编码] = '" &  dr("项目编码") &  "'", "[_SortKey]")
        For  i As  Integer = 1 To drs.Count - 1
            drs(i)("累计_计量金额") = drs(i-1)("累计_计量金额") + drs(i)("申请金额")
            drs(i)("累计_扣款金额") = drs(i-1)("累计_扣款金额") + drs(i)("本期_累计扣款")         '+ drs(i)("申请金额") - drs(i)("本期_累计扣款")
            drs(i)("累计_支付金额") = drs(i-1)("累计_支付金额") + drs(i)("实际支付金额")
        Next
        If  e.DataCol.Name = "项目编码" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
            dr = e.DataTable.Find("[_SortKey] < " & mr("_SortKey") &  " And [项目编码] = '" &  e.OldValue &  "'", "[_SortKey] Desc")
            If  dr Is Nothing Then
                dr = e.DataTable.Find("[项目编码] = '" & e.OldValue &  "'", "[_SortKey]")
                If dr IsNot Nothing Then
                    dr("累计_计量金额") = dr("申请金额")
                    dr("累计_扣款金额") = dr("本期_累计扣款")
                    dr("累计_支付金额") = dr("实际支付金额")    ' dr("申请金额") - dr("本期_累计扣款")
                End If
            End If
            If  dr IsNot Nothing Then
                drs = e.DataTable.Select("[_SortKey] >= " & dr("_SortKey") &  " And [项目编码] = '" &  dr("项目编码") &  "'", "[_SortKey]")
                For i As Integer = 1 To drs.Count - 1
                    drs(i)("累计_计量金额") = drs(i-1)("累计_计量金额") + drs(i)("申请金额")
                    drs(i)("累计_扣款金额") = drs(i-1)("累计_扣款金额") + drs(i)("本期_累计扣款")         '+ drs(i)("申请金额") - drs(i)("本期_累计扣款")
                    drs(i)("累计_支付金额") = drs(i-1)("累计_支付金额") + drs(i)("实际支付金额")
                Next
            End If
        End If
End Select

 上传对应实例测试。

老师，示例已上传，工程款管理是根据工程款申请，提交申请后，引用过去的。

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目4.table

默认是根据_Sortkey列排序的，你现在想根据期数列排序？

 

'累计计量?扣款?支付
Select Case  e.DataCol.Name
    Case  "项目编码","申请金额","本期_累计扣款","实际支付金额",    "企业所得税率","个人所得税基数","个人所得税率","管理费费率" , "手续费","安全保证金","质量保证金","应急保证金","资料保证金","fa piao金额","到账金额","成本fa piao","人工工资","其他费用","抵扣增值税","现金补税","预交增值税","应补增值税","管理费","应扣费用明细_小计","实际支付金额","本期_累计扣款","成本差额税率","成本差额税","扣税明细_小计","成本差额"
        Dim  dr As  DataRow
        Dim  mr As  DataRow = e.DataRow
        Dim  drs As  List(of DataRow)
        dr = e.DataTable.Find("[计量期数] < " & mr("计量期数") &  " And [项目编码] = '" &  mr("项目编码") &  "'", "[计量期数] Desc")
        If dr Is Nothing Then
            mr("累计_计量金额") = mr("申请金额")
            mr("累计_扣款金额") = mr("本期_累计扣款")
            mr("累计_支付金额") = mr("实际支付金额")  '("申请金额") - mr("本期_累计扣款")
            dr = mr
        End If
        drs = e.DataTable.Select("[计量期数] >= " & dr("计量期数") &  " And [项目编码] = '" &  dr("项目编码") &  "'", "[计量期数]")
        For  i As  Integer = 1 To drs.Count - 1
            drs(i)("累计_计量金额") = drs(i-1)("累计_计量金额") + drs(i)("申请金额")
            drs(i)("累计_扣款金额") = drs(i-1)("累计_扣款金额") + drs(i)("本期_累计扣款")         '+ drs(i)("申请金额") - drs(i)("本期_累计扣款")
            drs(i)("累计_支付金额") = drs(i-1)("累计_支付金额") + drs(i)("实际支付金额")
        Next
        If  e.DataCol.Name = "项目编码" AndAlso e.OldValue IsNot Nothing AndAlso e.OldValue <> e.NewValue Then
            dr = e.DataTable.Find("[计量期数] < " & mr("计量期数") &  " And [项目编码] = '" &  e.OldValue &  "'", "[计量期数] Desc")
            If  dr Is Nothing Then
                dr = e.DataTable.Find("[项目编码] = '" & e.OldValue &  "'", "[计量期数]")
                If dr IsNot Nothing Then
                    dr("累计_计量金额") = dr("申请金额")
                    dr("累计_扣款金额") = dr("本期_累计扣款")
                    dr("累计_支付金额") = dr("实际支付金额")    ' dr("申请金额") - dr("本期_累计扣款")
                End If
            End If
            If  dr IsNot Nothing Then
                drs = e.DataTable.Select("[计量期数] >= " & dr("计量期数") &  " And [项目编码] = '" &  dr("项目编码") &  "'", "[计量期数]")
                For i As Integer = 1 To drs.Count - 1
                    drs(i)("累计_计量金额") = drs(i-1)("累计_计量金额") + drs(i)("申请金额")
                    drs(i)("累计_扣款金额") = drs(i-1)("累计_扣款金额") + drs(i)("本期_累计扣款")         '+ drs(i)("申请金额") - drs(i)("本期_累计扣款")
                    drs(i)("累计_支付金额") = drs(i-1)("累计_支付金额") + drs(i)("实际支付金额")
                Next
            End If
        End If
End Select

对的，我是根据项目编码的期数列排序。

参考7楼代码修改。把_sortkey替换成你的排序列。

老师，之前都是对的，现在报错

此主题相关图片如下：qq截图20170928150606.jpg