- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 再次讨教：三表汇总-已解决 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=34051)
-- 发布时间：2013/5/29 16:46:00
-- 再次讨教：三表汇总-已解决
首先感谢先前各位对我之前相关提问的解答
由于高手给的代码都是数组的，看不太懂
对于更深的问题，只能发帖求助
有3个表，各字段如图 要汇总成一个总表，显示如图的结构
组合表里，毛坯库存=毛坯入库数量-电镀出库数量
电镀件库存=电镀出库数量-生产领用数量
此主题相关图片如下：1.png
此主题相关图片如下：4.png
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:三表汇总 (2).table |
[此贴子已经被作者于2013-6-2 17:44:17编辑过]
-- 发布时间：2013/5/29 16:48:00
--
看不懂你的问题，三表之间的关系、怎么个合并法？ 你得说明清楚。
-- 发布时间：2013/5/29 17:30:00
--
根据日期的顺序，将各表按图2的顺序组合起来，图中相对颜色更深的列是计算式，公式在贴子里有说明
-- 发布时间：2013/5/29 17:35:00
--
呵呵，如果这样，毫无难度可言啊，参考：
http://www.foxtable.com/help/topics/1533.htm
针对这三个表，分别写三次类似的代码就行了：
Dim Cols1() As String = {"来源列一","来源列二","来源列三"}
Dim Cols2() As String = {"接收列一","接收列二","接收列三"}
For Each dr1 As DataRow In DataTables("表A").DataRows
Dim dr2 As DataRow = DataTables("表B").AddNew()
For i As Integer = 0 To Cols1.Length -1
dr2(Cols2(i)) = dr1(Cols1(i))
Next
Next
-- 发布时间：2013/5/29 18:29:00
--
狐爸这个代码不对吧
我是要把3个表按日期排序后，组合到一个大表里
3个表各自有的日期，会合并、去重后作为日期列的分组依据的
-- 发布时间：2013/5/29 18:31:00
--
http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=33296&page=1&star=1
这是以前的帖子 问题和这个类似 更简单些
数组看不太懂 不会举一反三
-- 发布时间：2013/5/29 19:06:00
--
发上来的文件没有数据源，打不开
-- 发布时间：2013/5/29 19:19:00
--
以下是引用有点甜在2013-5-29 19:06:00的发言：
发上来的文件没有数据源，打不开
不好意思，已重新上传
发上来的文件没有数据源，打不开
-- 发布时间：2013/5/29 20:42:00
--
建立一个时间表只需要包含三表中的不重复时间列
以这个表为主表，关联其他三个表，就可以拼凑成你想要的表了
-- 发布时间：2013/5/30 0:45:00
--
这个应该再没疑问了吧,再搞不定确实该打PP了
Dim v1 As String() = {"入库日期","入库单号","数量","单价","iif(数量 is null,0,数量)*iif(单价 is null,0,单价)"} \'公式
Dim v2 As String() = {"入库日期","入库单号","数量","单价","iif(数量 is null,0,数量)*iif(单价 is null,0,单价)","0.1*0"}
Dim v3 As String() = {"出库日期","出库单号","数量","单价","iif(数量 is null,0,数量)*iif(单价 is null,0,单价)","0.1*0"}
Dim v11 As String() = {"日期","毛坯入库单号","毛坯入库数量","毛坯入库单价","毛坯入库金额"} \'显示列
Dim v22 As String() = {"日期","电镀出库单号","电镀出库数量","电镀出库单价","电镀出库金额","毛坯库存"}
Dim v33 As String() = {"日期","生产领用单号","领用数量","领用单价","领用金额","电镀件库存"}
Dim dt1,dt2,dt3 As fxDataSource
Dim jb As SQLJoinTableBuilder
Dim s1,s2 As String
Dim n1 As Integer
Dim t1 As Table
Dim r1 As Row
n1=0
jb =new SQLJoinTableBuilder("查询", "毛胚表")
\'jb.C
For Each s1 In v1
jb.AddExp(v11(n1),s1)
n1+=1
Next
dt1=jb.BuildDataSource()
n1=0
jb =new SQLJoinTableBuilder("查询", "电镀表")
\'jb.C
For Each s1 In v2
jb.AddExp(v22(n1),s1)
n1+=1
Next
dt2=jb.BuildDataSource()
n1=0
jb =new SQLJoinTableBuilder("查询", "出库表")
\'jb.C
For Each s1 In v3
jb.AddExp(v33(n1),s1)
n1+=1
Next
dt3=jb.BuildDataSource()
s1 = "日期"
dt1.Combine(s1,dt2,s1)
dt1.Combine(s1,dt3,s1)
dt1.show("统计")
t1 =Tables("统计")
t1.Redraw = 0
t1.sort = s1
For Each r As Row In t1.Rows
If r.Index= 0 Then
r("毛坯库存") = r("毛坯入库数量")-r("电镀出库数量")
r("电镀件库存") = r("电镀出库数量") -r("领用数量")
Else
r("毛坯库存") =t1(r.Index-1,"毛坯库存")+ r("毛坯入库数量")-r("电镀出库数量")
r("电镀件库存") = t1(r.Index-1,"电镀件库存")+ r("电镀出库数量") -r("领用数量")
End If
Next
Dim g As Subtotalgroup
t1.SubtotalGroups.Clear()
t1.GroupAboveData = False
t1.TreeVisible = False
t1.SpillNode = True
g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "毛坯入库数量,毛坯入库金额,电镀出库数量,电镀出库金额,领用数量,领用金额"
g.Caption = "总计"
t1.SubtotalGroups.Add(g)
t1.Subtotal()
n1 = t1.Rows.Count(True) - 1
r1 = t1.Rows(n1,True)
r1("毛坯库存") = r1("毛坯入库数量")-r1("电镀出库数量")
r1("电镀件库存") =r1("电镀出库数量") -r1("领用数量")
t1.Redraw =1
t1 =Tables("窗口1_table1")
t1.Redraw = 0
t1.DataSource = DataTables("统计")
t1.sort = s1
For Each c As Col In t1.Cols
If c.name.Contains("金额") Then c.DataCol.SetFormat("0.00")
Next
t1.SubtotalGroups.Clear()
t1.GroupAboveData = False
t1.TreeVisible = False
t1.SpillNode = True
g= New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "毛坯入库数量,毛坯入库金额,电镀出库数量,电镀出库金额,领用数量,领用金额"
g.Caption = "总计"
t1.SubtotalGroups.Add(g)
t1.Subtotal()
n1 = t1.Rows.Count(True) - 1
r1 = t1.Rows(n1,True)
r1("毛坯库存") = r1("毛坯入库数量")-r1("电镀出库数量")
r1("电镀件库存") =r1("电镀出库数量") -r1("领用数量")
t1.Redraw =1
[此贴子已经被作者于2013-5-30 7:09:24编辑过]