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

Dim bd1 As New SQLGroupTableBuilder("统计表1","工具入库")

Dim dt1 As fxDataSource

bd1.C

bd1.Groups.AddDef("工具名称") \'根据型号分组

bd1.Groups.AddDef("工具型号或尺寸") \'根据型号分组

bd1.Totals.AddDef("入库数量","进货_数量") \'对数量进行统计

bd1.Totals.AddDef("单把价格",AggregateEnum.Average)

dt1 = bd1.BuildDataSource()

Dim bd2 As New SQLGroupTableBuilder("统计表2","工具领用")

Dim dt2 As fxDataSource

bd2.C

bd2.Groups.AddDef("工具名称") \'根据型号分组

bd2.Groups.AddDef("工具型号或尺寸") \'根据型号分组

bd2.Totals.AddDef("领用数量","领用总数量") \'对数量进行统计

bd2.Totals.AddDef("损坏数量","损坏总数量") \'对数量进行统计

bd2.Totals.AddDef("归还数量","归还总数量") \'对数量进行统计

在这个表里有个逻辑列，列名：是否归还

dt2 = bd2.BuildDataSource()

Dim bd3 As New SQLGroupTableBuilder("统计表3","工具统计")

Dim dt3 As fxDataSource

bd3.C

bd3.Groups.AddDef("工具名称") \'根据型号分组

bd3.Groups.AddDef("工具型号或尺寸") \'根据型号分组

bd3.Totals.AddDef("初始库存数量","原有数量") \'对数量进行统计

dt3 = bd3.BuildDataSource()

Dim nms As String() = { "工具名称" , "工具型号或尺寸" }

dt1.Combine(nms,dt2,nms) \'将销售统计数据组合到进货统计数据

dt1.Combine(nms,dt3,nms) \'将退货统计数据组合到进货统计数据

Tables("窗口2_Table1").DataSource = dt1 \'将统计结果绑定到Table

With DataTables("窗口2_Table1").DataCols \'用表达式列计算库存数据

.Add("报废_数量",Gettype(Integer), "IsNull([进货_数量],0)-IsNull([损坏总数量],0)")

.Add("库存_数量",Gettype(Integer), "IsNull([报废_数量],0)+IsNull([原有数量],0)-IsNull([领用总数量],0)+IsNull([归还总数量],0)")

.Add("库存_金额",Gettype(Double), "[库存_数量]*[单把价格]")

Tables("窗口2_Table1").Cols.Remove("报废_数量","原有数量","领用总数量","损坏总数量","归还总数量","进货_数量")

End With