专家坐堂-跨表统计的问题Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  跨表统计的问题  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=4016)

--  作者：blackzhu
--  发布时间：2009/8/24 10:06:00
--  跨表统计的问题
这个是跨表统计的代码:

Dim T2 As DataRow = e.DataRow
Dim Sum As Double
Select Case e.DataCol.Name
    Case "IO","口岸","色号","色名","运输方式","包装"
        Sum = DataTables("装箱单").Compute("Sum(XS)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(S)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(M)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(L)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(XL)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(XXL)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        Sum = DataTables("装箱单").Compute("Sum(XXXL)","[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'")
        e.DataRow("XS") = Sum
        e.DataRow("S") = Sum
        e.DataRow("M") = Sum
        e.DataRow("L") = Sum
        e.DataRow("XL") = Sum
        e.DataRow("XXL") = Sum
        e.DataRow("XXXL") = Sum
End Select

这个是自动计算的代码:

Dim T3 As DataRow = e.DataRow
Select Case e.DataCol.Name
    Case "IO","口岸","色号","色名","运输方式","包装"
        \'在表A中找出同一分类的行
        T3 = DataTables("装箱统计").Find("[IO] = \'" & T3("IO") & "\' And [口岸] = \'" & T3("口岸") & "\'And [色号] = \'" & T3("色号") & "\'And [色名] = \'" & T3("色名") & "\'And [运输方式] = \'" & T3("运输方式") & "\'and[包装]=\'" & T3("包装") & "\'")
        If T3 IsNot Nothing Then \'如果找到
            \'针对该行的分类列,触发DataColChanged事件.
            DataTables("装箱统计").DataCols("IO").RaiseDataColChanged(T3)
        End If
End Select

请看看是怎么回事?我统计全部为零.

--  作者：yangming
--  发布时间：2009/8/24 10:07:00
--

上传文件看看吧

--  作者：blackzhu
--  发布时间：2009/8/24 10:42:00
--

以下是引用yangming在2009-8-24 10:07:00的发言：

上传文件看看吧

代码有问题的,我的文件有SQL外部数据源的.

--  作者：blackzhu
--  发布时间：2009/8/24 10:44:00
--
这个代码是不是要在统计表中将所有涉及的列全部输入数据后才能显示.

--  作者：czy
--  发布时间：2009/8/24 10:46:00
--
这样？

Dim T2 As DataRow = e.DataRow
Dim Str As String
Str = "[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'"
Select Case e.DataCol.Name
    Case "IO","口岸","色号","色名","运输方式","包装"
        T2("XS") = DataTables("装箱单").Compute("Sum(XS)",Str)
        T2("S") = DataTables("装箱单").Compute("Sum(S)",Str)
        T2("M") = DataTables("装箱单").Compute("Sum(M)",Str)
        T2("L") = DataTables("装箱单").Compute("Sum(L)",Str)
        T2("XL") = DataTables("装箱单").Compute("Sum(XL)",Str)
        T2("XXL") = DataTables("装箱单").Compute("Sum(XXL)",Str)
        T2("XXXL") = DataTables("装箱单").Compute("Sum(XXXL)",Str)
End Select
[此贴子已经被作者于2009-8-24 10:48:24编辑过]

--  作者：chenwuwenyu
--  发布时间：2009/8/24 10:50:00
--
你sum只能得到一个值吧？
5楼代码应该是可以的

--  作者：blackzhu
--  发布时间：2009/8/24 10:53:00
--

以下是引用czy在2009-8-24 10:46:00的发言：
这样？

Dim T2 As DataRow = e.DataRow
Dim Str As String
Str = "[IO] = \'" & T2("IO") & "\' And [口岸] = \'" & T2("口岸") & "\'And [色号] = \'" & T2("色号") & "\'And [色名] = \'" & T2("色名") & "\'And [运输方式] = \'" & T2("运输方式") & "\'and[包装]=\'" & T2("包装") & "\'"
Select Case e.DataCol.Name
    Case "IO","口岸","色号","色名","运输方式","包装"
        T2("XS") = DataTables("装箱单").Compute("Sum(XS)",Str)
        T2("S") = DataTables("装箱单").Compute("Sum(S)",Str)
        T2("M") = DataTables("装箱单").Compute("Sum(M)",Str)
        T2("L") = DataTables("装箱单").Compute("Sum(L)",Str)
        T2("XL") = DataTables("装箱单").Compute("Sum(XL)",Str)
        T2("XXL") = DataTables("装箱单").Compute("Sum(XXL)",Str)
        T2("XXXL") = DataTables("装箱单").Compute("Sum(XXXL)",Str)
End Select
[此贴子已经被作者于2009-8-24 10:48:24编辑过]

C版,你的公式是不是放在装箱统计表中,我放入后显示全部为空?

--  作者：blackzhu
--  发布时间：2009/8/24 11:03:00
--
C版我查过了,必须要将装箱统计中列输入全部输入和装箱单匹配的数据才能显示,但表达式是不需要的,请问这个有办法想吗?
另外我想问一下,如T2("XXXL") = DataTables("装箱单").Compute("Sum(XXXL)",Str)这句代码,我这个T2(XXXL)=DataTables("装箱单").Compute("Sum(XXXL)",Str)*(箱数)的,这个公式怎么写?

--  作者：blackzhu
--  发布时间：2009/8/24 11:06:00
--
还有我的自动计算更新的代码有问题吗?

--  作者：czy
--  发布时间：2009/8/24 11:48:00
--

以下是引用blackzhu在2009-8-24 11:03:00的发言：
C版我查过了,必须要将装箱统计中列输入全部输入和装箱单匹配的数据才能显示,但表达式是不需要的,请问这个有办法想吗?
另外我想问一下,如T2("XXXL") = DataTables("装箱单").Compute("Sum(XXXL)",Str)这句代码,我这个T2(XXXL)=DataTables("装箱单").Compute("Sum(XXXL)",Str)*(箱数)的,这个公式怎么写?

应该是代码涉及的任一列有变动都会触发的。但要满足你设置的条件才能有结果。
箱数是当前表吗？
如果是当前表， *T2(”箱数“)

[此贴子已经被作者于2009-8-24 11:51:03编辑过]