主题：[求助]如何用代码实现跨表多条件统计？请指教！

将“销售表”中，某客户编号(如8077)在某时间段内（如一月份）的发货金额，自动汇总到“统计表”表对应的列中，用代码如何实现，并在，“统计表”中的“客户编号”列输入客户编号(如:8057)时，能自动在客户编号8057所在行对应的列统计出该月的发货金额，请赐教！

 

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

点击浏览该文件:测试项目.table

直接用交叉统计就可以了，如：

 

Dim g As New CrossTableBuilder("统计表1", DataTables("销售表"), "[店面编号] = '8077'")
g.HGroups.AddDef("店面编号")
g.VGroups.AddDef("发货日期", "发货金额统计_{0}月")
g.Totals.AddDef("发货金额", "发货金额")
g.Filter = "[店面编号] = '8077'"
g.Build()
MainTable = Tables("统计表1")

 

或者：

 

Dim d1 As Date
Dim d2 As Date
Dim nms() As String  = {"本日销量","本周销量","本旬销量","本月销量","本年销量"}
For Each dr As DataRow In DataTables("日报表").DataRows
    For Each nm As String In nms
        Select Case nm
            Case "本日销量"
                d1 = Date.Today
                d2 = Date.Today
            Case "本周销量"
                Dim n As Integer = Date.Today.DayOfWeek
                d1 = Date.Today.AddDays(-n)
                d2 = Date.Today.AddDays(6-n)
            Case "本旬销量"
                Dim n As Integer = Date.Today.Day
                Dim y As Integer = Date.Today.Year
                Dim m As Integer = Date.Today.Month
                Select n
                    Case 1 To 10
                        d1 = new Date(y,m,1)
                        d2 = new Date(y,m,10)
                    Case 11 To 20
                        d1 = new Date(y,m,11)
                        d2 = new Date(y,m,20)
                    Case Else
                        d1 = new Date(y,m,21)
                        d2 = new Date(y,m,Date.DaysInMonth(y,m))
                End Select
            Case "本月销量"
                Dim y As Integer = Date.Today.Year
                Dim m As Integer = Date.Today.Month
                d1 = New Date(y,m,1)
                d2 = new Date(y,m,Date.DaysInMonth(y,m))
            Case "本年销量"
                Dim y As Integer = Date.Today.Year
                d1 = New Date(y,1,1)
                d2 = new Date(y,12,Date.DaysInMonth(y,12))
               
        End Select
        Dim f As String = "销售日期 >= #" & d1 & "# And 销售日期 <= #" & d2 & "#"
        dr(nm) = DataTables("销售表").Compute("sum(数量)","产品 = '" & dr("产品") & "' And " & f)
    Next
Next

 

 

 

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

点击浏览该文件:统计测试.table

谢谢，由于销售表的数据是在不断更新的，所以希望在销售表数据更新后统计表能自动汇总，同时希望能统计所有店面的数据，怎么实现呢

 

那就实在MainTableChanged事件中设置代码，每次切换到这个日报表，就自动重新统计。

不好意思，好像刚传的附件传错了，应该是这个项目。

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

点击浏览该文件:测试项目.table

用交叉统计就行，做个按钮，按钮代码：

 

Dim g As New CrossTableBuilder("统计表1", DataTables("销售表"))
g.HGroups.AddDef("店面编号")
g.VGroups.AddDef("发货日期", "发货金额_{0}月")
g.Totals.AddDef("发货金额", "发货金额")
g.Build()
MainTable = Tables("统计表1")