关于多个联接列 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim bd1 As New GroupTableBuilder("统计表1",DataTables("费用明细附表"))
Dim dt1 As fxDataSource
bd1.Groups.AddDef("日期",DateGroupEnum.None)
bd1.Groups.AddDef("部门")
bd1.Groups.AddDef("成本类别")
bd1.Totals.AddDef("费用_本月")
bd1.Totals.AddDef("费用_累计")
dt1 = bd1.BuildDataSource()

Dim bd2 As New GroupTableBuilder("统计表2",DataTables("利润表附表"))
Dim dt2 As fxDataSource
bd2.Groups.AddDef("日期",DateGroupEnum.None)
bd2.Groups.AddDef("部门")
bd2.Totals.AddDef("主营业务收入_本月")
bd2.Totals.AddDef("主营业务收入_累计")
dt2 = bd2.BuildDataSource()

Dim nms As String() = {"日期","部门"}
dt1.Combine(nms,dt2,nms)
dt1.Show("统计表1") '显示统计结果

Dim t As Table = Tables("统计表1")
t.DataTable.DataCols.Add("费用率_本月",Gettype(Double))
t.DataTable.DataCols.Add("费用率_累计",Gettype(Double))

Dim n1,n2 As Double
For Each r As Row In t.Rows
    If r.IsNull("主营业务收入_本月") = False Then
        n1 = r("主营业务收入_本月")
        n2 = r("主营业务收入_累计")
    End If
    r("费用率_本月") = r("费用_本月") / n1
    r("费用率_累计") = r("费用_累计") / n2
Next

t.DataTable.DataCols("费用率_本月").SetFormat("0.00%")
t.DataTable.DataCols("费用率_累计").SetFormat("0.00%")

多谢多谢，终于搞定了这张表，报表方面基本完成，项目完成大半了，接下去要弄下图表了

Dim Filter As String '设置筛选条件

With e.Form.Controls("DW")

If .Value IsNot Nothing Then

Filter = "所属单位 = '" & .Value & "'"

End If

End With

With e.Form.Controls("NF") '在前一筛选条件控制下设置第二项筛选条件,其内容根据第一列的变化而变化

If .Value IsNot Nothing Then

If Filter > "" Then

Filter = Filter & " And "

End If

Filter = Filter & "年 = '" & .Value & "'"

End If

End With

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

Dim bd1 As New SQLGroupTableBuilder("费用占比(小类)","部门损益总账")

Dim dt1 As fxDataSource

bd1.AddTable("部门损益总账","部门名称","部门档案","部门名称")

bd1.AddTable("部门损益总账","科目编号","科目档案","科目编码")

bd1.Groups.AddDef("所属单位")

bd1.Groups.Adddef("所属日期",DateGroupEnum.year,"年")

bd1.Groups.Adddef("所属日期",DateGroupEnum.none)

bd1.Groups.AddDef("二级科目","成本小类")

bd1.Totals.AddDef("借方发生","本月_费用")

bd1.Decimals = 0

bd1.Filter = "成本科目 = True And (" & filter & ")"

dt1 = bd1.BuildDataSource()

bd1.build

Dim bd2 As New SQLgroupTableBuilder("统计表2","部门损益总账")

Dim dt2 As fxDataSource

bd2.AddTable("部门损益总账","部门名称","部门档案","部门名称") '添加统计表

bd2.AddTable("部门损益总账","科目名称","科目档案","科目名称") '添加统计表

bd2.Groups.AddDef("所属单位")

bd2.Groups.Adddef("所属日期",DateGroupEnum.year,"年")

bd2.Groups.Adddef("所属日期",DateGroupEnum.none)

bd2.Groups.AddDef("一级科目")

bd2.Totals.AddDef("借方发生","主营业务收入")

bd2.Filter = "一级科目 = '主营业务收入' and (" & filter & ")"

dt2 = bd2.BuildDataSource()

Dim nms As String() = {"所属日期","年","所属单位"}

dt1.Combine(nms,dt2,nms)

dt1.Show("费用占比(小类)") '显示统计结果

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

上述两段代码中加了Groups.Adddef("所属日期",DateGroupEnum.year,"年")后，Dim nms As String() = {"所属日期","年","所属单位"}总是报错，请帮忙看看有什么问题

说明你表里面就是没有叫做所属日期的列。

不会做，上传例子。

bd1.Groups.Adddef("所属日期",DateGroupEnum.year,"年份")

bd2.Groups.Adddef("所属日期",DateGroupEnum.year,"年份")

应该是这两句代码造成的

下载信息 [文件大小：下载次数： ]
点击浏览该文件:管理项目2.foxdb

Dim Filter As String = "1=1" '设置筛选条件
With e.Form.Controls("DW")
    If .Value IsNot Nothing Then
        Filter &= " and 所属单位 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("NF") '在前一筛选条件控制下设置第二项筛选条件,其内容根据第一列的变化而变化
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Dim d As Date = new Date(.value, 1, 1)
        Filter = Filter & "所属日期 >= #" & d & "# and 所属日期 < #" & d.AddYears(1) & "#"
    End If
End With

好的。谢谢

还不太明白，先消化一下

再求助，以下两节代码中，统计列最后生成的列名都是“借方发生_1”和“借方发生_2”,如何区别一下，直接这样组合不起来，

Dim bd1 As New SQLcrossTableBuilder("费用占比(小类)","部门损益总账")

Dim dt1 As fxDataSource

bd1.AddTable("部门损益总账","部门名称","部门档案","部门名称")

bd1.AddTable("部门损益总账","科目编号","科目档案","科目编码")

bd1.HGroups.AddDef("所属单位")

bd1.HGroups.Adddef("所属日期",DateGroupEnum.none)

bd1.HGroups.AddDef("二级科目","成本小类")

bd1.VGroups.Adddef("所属日期",DateGroupEnum.year,"{0}_费用")

bd1.Totals.AddDef("借方发生")

bd1.Decimals = 0

dt1 = bd1.BuildDataSource()

bd1.build

Dim bd2 As New SQLCrossTableBuilder("统计表2","部门损益总账")

Dim dt2 As fxDataSource

bd2.AddTable("部门损益总账","部门名称","部门档案","部门名称") '添加统计表

bd2.AddTable("部门损益总账","科目名称","科目档案","科目名称") '添加统计表

bd2.HGroups.AddDef("所属单位")

bd2.HGroups.Adddef("所属日期",DateGroupEnum.none)

bd2.HGroups.AddDef("一级科目")

bd2.VGroups.Adddef("所属日期",DateGroupEnum.year,"{0}_收入")

bd2.Totals.AddDef("借方发生")

dt2 = bd2.BuildDataSource()

Dim nms As String() = {"所属日期","所属单位"}

dt1.Combine(nms,dt2,nms)

dt1.Show("费用占比(小类)")

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

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

交叉统计表不能这样用。

如果要做，只能用代码处理

http://www.foxtable.com/help/topics/2394.htm

主题：关于多个联接列