再次讨教：三表汇总-已解决 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

这个应该再没疑问了吧,再搞不定确实该打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

dt1=jb.BuildDataSource()

n1=0

jb =new SQLJoinTableBuilder("查询", "电镀表")

'jb.C

For Each s1 In v2

jb.AddExp(v22(n1),s1)

n1+=1

dt2=jb.BuildDataSource()

n1=0

jb =new SQLJoinTableBuilder("查询", "出库表")

'jb.C

For Each s1 In v3

jb.AddExp(v33(n1),s1)

n1+=1

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

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")

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编辑过]

把jspta兄的自定义函数[三表汇总]改为以下似乎也可以达到jspta兄原来的效果

但我不解的是: 看不出如何向[三表汇总]表赋值? 望Jspta兄解惑,多谢!

Dim SQL As String

Dim arr1() As String = {"毛胚表","电镀表","出库表"}

Dim arr2() As String = {"入库日期","入库日期","出库日期"}

For IntA As Integer = 0 To 2

SQL+ = " Union Select " & arr2(IntA) & " As 时间 from {" & arr1(IntA) & "}"

Dim cmd As new SQLCommand

cmd.CommandText = SQL.substring(7)

Dim t As Table = Tables("时间表")

t.StopRedraw

t.DataTable.DataRows.clear

Dim f As New Filler

f.SourceTable = cmd.ExecuteReader

f.DataTable = t.DataTable '指定数据接收表

f.Fill() '填充数据

t.DataTable.Load

t.ResumeRedraw

验证是下面这句起作用,不解?

t.DataTable.Load

OK,原来如此,多谢!

真是昏啊!你详尽交待问题及要求再说吧,你浪费别人多少宝贵的时间!

你最初(5月15的帖子)发贴时,已提醒你可能存在同日多条记录情况,及给出方法,但你置之不理!

以下是引用泡泡在2013-6-1 10:50:00的发言：

那段代码我看到的，但他的汇总是以同日多条入库单号一致的前提做的

我当天的不同入库单有不同单号的 RK001 RK002这样的，没法汇总为一条记录

你做個你想要的結果出來吧!

昏,你不會把結果表弄進文件嗎?準備功夫要做足!

你以為隨便改改就OK?原來的是以[不重複日期]為關聯列的,你現在日期重複了,幾乎推倒重來了

套用灰太狼經典台詞:我一定會回來的!

樓主標題註明已解決,我看未必:

因現在代碼中沒有加入時段限制,為合併三表所有數據!

日後數據庫累積了許多年月的數據,難道每次都要查看或列印全部數據?

fjlclxj的代碼不易理解,我把我做的也傳上來:四個表結構做了改動,代碼相對簡單些

這個例子有點意思:1.去重合併三表日期的方法值得思量;

2.如把三表數據全部填充後,再去除重複行的方法值得注意:先標記,後刪除(如遍歷時就刪除,很難調節)

下载信息 [文件大小：下载次数： ]
点击浏览该文件:三表汇总 .table

[此贴子已经被作者于2013-6-3 16:21:42编辑过]

主题：再次讨教：三表汇总-已解决