主题：求助这种表怎么设计

 每一笔佣金，都应该加上日期，这样根据日期统计

 做个按钮，代码

 

Dim dt As DataTable = DataTables("佣金统计表")
dt.DataRows.Clear

Dim d1 As Date = new Date(2014,5,29)
Dim d2 As Date = new Date(2014,6,28)
Dim filter1 As String = "结佣日 >= #" & d1 & "# and 结佣日 <= #" & d2 & "#"
Dim filter2 As String = "离职日期 >= #" & d1 & "# and 离职日期 <= #" & d2 & "# or 上岗日期 >= #" & d1 & "# and 离职日期 is null"

For Each dr As DataRow In DataTables("员工管理").Select(filter2)
    Dim ndr As DataRow = dt.AddNew
    ndr("员工姓名") = dr("姓名")
    ndr("门店") = dr("门店")
    ndr("职务") = dr("职务")
    ndr("佣金") = DataTables("佣金明细").Compute("sum(佣金)", filter1 & " and 员工姓名 = '" & dr("姓名") & "'")
    ndr("结佣日") = DataTables("佣金明细").Compute("max(结佣日)", filter1 & " and 员工姓名 = '" & dr("姓名") & "'")
Next

 呃，你先看懂下面的代码吧。如果要用sql语句来做，条件直接用filter1、filter2就行。

 

Dim dt As DataTable = DataTables("佣金统计表")
dt.DataRows.Clear

Dim d1 As Date = new Date(2014,5,29)
Dim d2 As Date = new Date(2014,6,28)
Dim filter1 As String = "结佣日 >= #" & d1 & "# and 结佣日 <= #" & d2 & "#"
Dim filter2 As String = "离职日期 >= #" & d1 & "# and 离职日期 <= #" & d2 & "# or 上岗日期 >= #" & d1 & "# and 离职日期 is null"

For Each dr As DataRow In DataTables("员工管理").Select(filter2)
    Dim ndr As DataRow = dt.AddNew
    ndr("员工姓名") = dr("姓名")
    ndr("门店") = dr("门店")
    ndr("职务") = dr("职务")
    ndr("佣金") = DataTables("佣金明细").Compute("sum(佣金)", filter1 & " and 员工姓名 = '" & dr("姓名") & "'")
    ndr("结佣日") = DataTables("佣金明细").Compute("max(结佣日)", filter1 & " and 员工姓名 = '" & dr("姓名") & "'")
Next

 请上传有数据的较完整的项目，才能测试或实现你的功能

 不明白意思，这样？

 

Dim dt As DataTable = DataTables("佣金统计表")
dt.DataRows.Clear
Dim d1 As Date = e.Form.Controls("DateTimePicker1").Value
Dim d2 As Date = e.Form.Controls("DateTimePicker2").Value
Dim filter1 As String = "结佣日 >= #" & d1 & "# and 结佣日 <= #" & d2 & "#"
Dim filter2 As String = "离职日期 >= #" & d1 & "# and 离职日期 <= #" & d2 & "# or 上岗日期 >= #" & d1 & "# and 离职日期 is null"
For Each dr As DataRow In DataTables("员工管理").Select(filter2)
    Dim ndr As DataRow = dt.AddNew
    ndr("员工姓名") = dr("姓名")
    ndr("门店") = dr("门店")
    ndr("职务") = dr("职务")
    ndr("佣金") = DataTables("佣金明细").Compute("sum(佣金)", filter1 & " and 置业顾问 = '" & dr("姓名") & "'")
    ndr("结佣日") = DataTables("佣金明细").Compute("max(结佣日)", filter1 & " and 置业顾问 = '" & dr("姓名") & "'")
Next

e.Form.Controls("Table1").Table.DataSource = dt

 你的例子，没有业绩表，怎么统计？

 先用22楼代码，生成表，然后再生成交叉统计表

 你给的例子，没有 佣金业绩表

 根据例子，代码这样写

 

Dim d1 As Date = new Date(2014,5,29)
Dim d2 As Date = new Date(2014,6,28)

 

Dim sql As String = "Select b.置业顾问, b.佣金, b.结佣日, b.类型, b.套数, a.门店, a.上岗日期, a.离职日期 from {员工管理} a right join {佣金明细} b on (a.姓名 = b.置业顾问) where 离职日期 >= #" & d1 & "# and 离职日期 <= #" & d2 & "# or 上岗日期 >= #" & d1 & "# and 离职日期 is null"
Dim g As New CrossTableBuilder("佣金业绩表",sql)
g.HGroups.AddDef("门店")
g.HGroups.AddDef("置业顾问")
g.VGroups.AddDef("类型")
g.Totals.AddDef("佣金", "佣金")
g.Totals.AddDef("套数", "套数")
g.OrderByTotal = True
g.HorizontalTotal = True
g.VerticalTotal = True
g.Decimals = 2
g.Subtotal = True

g.Build()

 代码如下

 

Dim d1 As Date = new Date(2014,5,29)
Dim d2 As Date = new Date(2014,6,28)

Dim sql As String = "Select a.姓名 as 置业顾问, b.佣金, b.结佣日, b.类型, b.套数, a.门店, a.上岗日期, a.离职日期 from {员工管理} a left join {佣金明细} b on (a.姓名 = b.置业顾问) where (离职日期 >= #" & d1 & "# or 离职日期 is null)"
Dim g As New CrossTableBuilder("佣金业绩表",sql)
g.HGroups.AddDef("门店")
g.HGroups.AddDef("置业顾问")
g.VGroups.AddDef("类型")
g.Totals.AddDef("佣金", "佣金")
g.Totals.AddDef("套数", "套数")
g.OrderByTotal = True
g.HorizontalTotal = True
g.VerticalTotal = True
g.Decimals = 2
g.Subtotal = True
g.filter = "结佣日 >= #" & d1 & "# and 结佣日 <= #" & d2 & "#"
g.Build()