Foxtable(狐表)用户栏目专家坐堂 → 同表,根据双列(品名和型号规格两列)累计数量


  共有1749人关注过本帖树形打印复制链接

主题:同表,根据双列(品名和型号规格两列)累计数量

帅哥哟,离线,有人找我吗?
xh2207
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:190 积分:1593 威望:0 精华:0 注册:2013/1/6 19:46:00
同表,根据双列(品名和型号规格两列)累计数量  发帖心情 Post By:2016/12/2 10:32:00 [只看该作者]

品名 型号规格 数量 本品名累计数量
AAa   2 2
BBb   2 2
AAa   2 4
BBb   10 12

的代码如下:

datacolchanged

 

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)
            dr("本品名型号规格累计") = Val1
        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)
                dr("本品名型号规格累计") = Val1
            Next
        End If
End Select

 

请问:根据品名和型号规格累计数量(即同品名和型号数量累加)的代码如何修改?谢谢!

要求的效果如下表:

品名 型号规格 数量 本品名累计数量 说明
AAa XX 2 2  
BBb HH 2 2  
AAa XX 2 4 第1行+第3行同品名规格累加
BBb HH 5 5 第2行+第4行同品名规格累加
AAa YY 2 2  
BBb GG 2 2  
AAa YY 4 6 第5行+第7行同品名规格累加
BBb GG 10 15 第6行+第8行同品名规格累加

 

 

 


 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105473 积分:536350 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2016/12/2 10:52: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("品名") & "' and 型号规格 = '" & e.DataRow("型号规格") & "'"
        drs = e.DataTable.Select(Filter)
        For Each dr As DataRow In drs
            Filter = "[_SortKey] <= " & dr("_SortKey") & " And [品名] = '" & dr("品名") & "' and 型号规格 = '" & dr("型号规格") & "'"
            Dim Val1 As Double = e.DataTable.Compute("Sum(数量)",Filter)
            dr("本品名型号规格累计") = Val1
        Next
        Filter = ""
        If e.DataCol.Name = "品名" AndAlso e.OldValue IsNot Nothing  Then
            Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [品名] = '" & e.OldValue & "' and 型号规格 = '" & e.DataRow("型号规格") & "'"
        ElseIf e.DataCol.Name = "型号规格" AndAlso e.OldValue IsNot Nothing  Then
            Filter = "[_SortKey] > " & e.DataRow("_SortKey") & " And [品名] = '" & e.DataRow("品名") & "' and 型号规格 = '" & e.OldValue & "'"
        End If
        If Filter > "" Then
            drs = e.DataTable.Select(Filter)
            For Each dr As DataRow In drs
                Filter = "[_SortKey] <= " & dr("_SortKey") & " And [品名] = '" & dr("品名") & "'and 型号规格 = '" & dr("型号规格") & "'"
                Dim Val1 As Double = e.DataTable.Compute("Sum(数量)",Filter)
                dr("本品名型号规格累计") = Val1
            Next
        End If
End Select

 回到顶部
帅哥哟,离线,有人找我吗?
xh2207
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:190 积分:1593 威望:0 精华:0 注册:2013/1/6 19:46:00
  发帖心情 Post By:2016/12/2 12:01:00 [只看该作者]

行,谢谢了!


 回到顶部