专家坐堂-统计组合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=183666)

--  作者：wlj88868
--  发布时间：2022/11/11 19:14:00
--  统计组合
查询后，单价怎么才可以合并显示在一起
此主题相关图片如下：1668165124017.png

--  作者：wlj88868
--  发布时间：2022/11/11 19:15:00
--

Dim bd1 As New GroupTableBuilder("统计表1", DataTables("出库明细"))
Dim dt1 As fxDataSource
Dim bd2 As New GroupTableBuilder("统计表2", DataTables("退货明细"))
Dim dt2 As fxDataSource
Dim Filter As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出库日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出库日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "所属商场 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "型号 = \'" & .Value & "\'"
    End If
End With
Dim Filter1 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "退货日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "退货日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "所属商场 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "型号 = \'" & .Value & "\'"
    End If
End With

bd1.Groups.AddDef("型号")\'根据型号分组
bd1.Groups.AddDef("单价")
bd1.Totals.AddDef("数量", "进货_数量")\'对数量进行统计
bd1.Totals.AddDef("金额", "进货_金额")\'对金额进行统计
bd1.filter = Filter
dt1 = bd1.BuildDataSource()
bd2.Groups.AddDef("型号")\'根据型号分组
bd2.Groups.AddDef("单价")
bd2.Totals.AddDef("数量", "退货_数量")\'对数量进行统计
bd2.Totals.AddDef("金额", "退货_金额")\'对金额进行统计
bd2.filter = Filter1
dt2 = bd2.BuildDataSource()
dt1.Combine("型号", dt2, "型号")\'将销售统计数据组合到进货统计数据
Tables("统计_Table1").DataSource = dt1 \'将统计结果绑定到Table
With DataTables("统计_Table1").DataCols \'用表达式列计算库存数据
    .Add("库存_数量", GetType(Integer), "IsNull([进货_数量],0)-IsNull([退货_数量],0)")
    .Add("库存_金额", GetType(Double), "[库存_数量] /[进货_数量] * [进货_金额]")
End With
Dim g As New SubtotalGroup \'定义一个新的分组
    g.GroupOn = "型号" \'分组列为客户列
    g.TotalOn = "进货_数量,进货_金额,退货_数量,退货_金额" \'对数量和金额进行统计
With Tables("统计_Table1")
     .SubtotalGroups.Clear() \'清除原有的分组
     .SubtotalGroups.Add(g) \'加入刚刚定义的分组
     .Subtotal() \'生成汇总模式
End With
Tables("统计_Table1").cols("型号").width = 130

--  作者：wlj88868
--  发布时间：2022/11/11 20:34:00
--

老师刚刚我想了一下，，后面的合并列就做到第一个里面，后面的不增加可以实现了，现在是不显示麻烦帮我看一下是那个地方的错误

Dim bd1 As New GroupTableBuilder("统计表1", DataTables("出库明细"))
Dim dt1 As fxDataSource
Dim bd2 As New GroupTableBuilder("统计表2", DataTables("退货明细"))
Dim dt2 As fxDataSource
Dim bd3 As New GroupTableBuilder("统计表3", DataTables("出库明细"))
Dim dt3 As fxDataSource
Dim Filter As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出库日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出库日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "所属商场 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "型号 = \'" & .Value & "\'"
    End If
End With
Dim Filter1 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "退货日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "退货日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "所属商场 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter1 > "" Then
            Filter1 = Filter1 & " And "
        End If
        Filter1 = Filter1 & "型号 = \'" & .Value & "\'"
    End If
End With

Dim Filter3 As String
With e.Form.Controls("startDate")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "出库日期 <= #" & .Value & "#"
    End If
End With
With e.Form.Controls("combobox1")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "所属商场 = \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("combobox2")
    If .Value IsNot Nothing Then
        If Filter3 > "" Then
            Filter3 = Filter3 & " And "
        End If
        Filter3 = Filter3 & "型号 = \'" & .Value & "\'"
    End If
End With

bd1.Groups.AddDef("型号")\'根据型号分组
bd1.Groups.AddDef("单价")
bd1.Groups.AddDef("品牌")
bd1.Groups.AddDef("所属商场")
bd1.Totals.AddDef("数量", "进货_数量")\'对数量进行统计
bd1.Totals.AddDef("金额", "进货_金额")\'对金额进行统计
bd1.filter = Filter
dt1 = bd1.BuildDataSource()
bd2.Groups.AddDef("型号")\'根据型号分组
bd2.Totals.AddDef("数量", "退货_数量")\'对数量进行统计
bd2.Totals.AddDef("金额", "退货_金额")\'对金额进行统计
bd2.filter = Filter1
dt2 = bd2.BuildDataSource()
bd3.Groups.AddDef("型号")\'根据型号分组
bd3.Totals.AddDef("数量", "期初_数量")\'对数量进行统计
bd3.Totals.AddDef("金额", "期初_金额")\'对金额进行统计
bd3.filter = Filter3
dt3 = bd3.BuildDataSource()
dt1.Combine("型号", dt2, "型号")\'将销售统计数据组合到进货统计数据
dt1.Combine("型号", dt3, "型号")
Tables("统计_Table1").DataSource = dt1 \'将统计结果绑定到Table
With DataTables("统计_Table1").DataCols \'用表达式列计算库存数据
    .Add("库存_数量", GetType(Integer), "IsNull([期初_数量], 0) + IsNull([进货_数量], 0) - IsNull([退货_数量], 0)")
    .Add("库存_金额", GetType(Double), "[库存_数量] /[进货_数量] * [进货_金额]")
End With
Dim g As New SubtotalGroup \'定义一个新的分组
    g.GroupOn = "型号" \'分组列为客户列
    g.TotalOn = "期初_数量,期初_金额,进货_数量,进货_金额,退货_数量,退货_金额,库存_数量,库存_金额" \'对数量和金额进行统计
With Tables("统计_Table1")
     .SubtotalGroups.Clear() \'清除原有的分组
     .SubtotalGroups.Add(g) \'加入刚刚定义的分组
     .Subtotal() \'生成汇总模式
End With
Tables("统计_Table1").cols("型号").width = 130
Tables("统计_Table1").Cols("期初_数量").Move(4)
Tables("统计_Table1").Cols("期初_金额").Move(5)

--  作者：wlj88868
--  发布时间：2022/11/11 20:35:00
--

此主题相关图片如下：1668170017352.png

--  作者：wlj88868
--  发布时间：2022/11/11 21:02:00
--
找到原因了，，不麻烦老师了，谢谢，，是时间的问题，，本月查询的时间没有这个型号的入库，所以查不到