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

应收查询（表A）代码：Select Case e.DataCol.name
    Case "吨位2","价格2","扣款2","吊杂费"
        Dim pr As DataRow
        pr = DataTables("总查询表").Find("客户 = '" & e.DataRow("托运单位") & "'")
        If pr IsNot Nothing Then
            pr("应收金额")= DataTables("应收查询").Compute("Sum(金额)","[托运单位] = '" & e.DataRow("托运单位") & "'")
        End If
End Select

扣款录入（表B) 代码：If e.DataCol.Name = "金额" Then
    Dim pr As DataRow
    pr = DataTables("总查询表").Find("客户 = '" & e.DataRow("欠款人") & "'")
    If pr IsNot Nothing Then
        pr("扣款金额")= DataTables("扣款录入").Compute("Sum(金额)","[欠款人] = '" & e.DataRow("欠款人") & "'")
    End If
End If

应付查询（表C) 代码：

Select Case e.DataCol.name
    Case "吨位","价格","扣款","吊杂费","业务费"
    Dim pr As DataRow
    pr = DataTables("总查询表").Find("客户 = '" & e.DataRow("承运单位") & "'")
    If pr IsNot Nothing Then
        pr("应付金额")= DataTables("应付查询").Compute("Sum(金额)","[承运单位] = '" & e.DataRow("承运单位") & "'")
    End If
End Select

已收款录入（表D）代码： If e.DataCol.Name = "金额" Then
    Dim pr As DataRow
    pr = DataTables("总查询表").Find("客户 = '" & e.DataRow("交款单位") & "'")
    If pr IsNot Nothing Then
        pr("已收款金额")= DataTables("已收款录入").Compute("Sum(金额)","[交款单位] = '" & e.DataRow("交款单位") & "'")
    End If
End If

总查询表（表E）代码：

If e.DataCol.Name = "客户" Then
    e.DataRow("应收金额") = Tables("应收查询").Compute("Sum(金额)","[托运单位] = '" & e.NewValue & "'")
    e.DataRow("扣款金额") = Tables("扣款录入").Compute("Sum(金额)","[欠款人] = '" & e.NewValue & "'")
    e.DataRow("应付金额") = Tables("应付查询").Compute("Sum(金额)","[承运单位] = '" & e.NewValue & "'")
    e.DataRow("已收款金额") = Tables("已收款录入").Compute("Sum(金额)","[交款单位] = '" & e.NewValue & "'")
    Dim tbs() As String = {"应收查询", "扣款录入", "应付查询", "已收款录入"}
    For Each tb As String In tbs
        If e.DataRow.isnull(tb) Then e.DataRow(tb) = 0

Next
End If

查询按钮代码：

Dim Filter As String = "1=1"
With e.Form.Controls("开始时间")
    If .Value IsNot Nothing Then
        Filter = Filter & " and 日期 >= #" & .Value & "#"
    End If
End With
With e.Form.Controls("客户")
    If .Value IsNot Nothing Then
        Filter = Filter & " and 客户 = '" & .Value & "'"
    End If
End With
With e.Form.Controls("结束时间")
    If .Value IsNot Nothing Then
        Filter = Filter & " and 日期 <= #" & .Value & "#"
    End If
End With
If Filter > "" Then
    Tables("应收查询").Filter = Filter.replace("客户", "托运单位")
    Tables("扣款录入").Filter = Filter.replace("客户", "欠款人")
    Tables("应付查询").Filter = Filter.replace("客户", "承运单位")
    Tables("已收款录入").Filter = Filter.replace("客户", "交款单位")
End If

Dim dt As DataTable = DataTables("总查询表")
dt.DataRows.Clear
Dim tbs() As String = {"应收查询", "扣款录入", "应付查询", "已收款录入"}
Dim xms() As String = {"托运单位", "欠款人", "承运单位", "交款单位"}
For i As Integer = 0 To tbs.length-1
    Dim tb = tbs(i)
    For Each xm As String In DataTables(tb).GetValues(xms(i), Tables(tb).Filter & " And " & xms(i) & " Is not null")
        Dim fdr As DataRow = dt.Find("客户 = '" & xm & "'")
        If fdr Is Nothing Then
            fdr = dt.AddNew
            fdr("客户") = xm
        End If
        If fdr.Isnull(tb & "金额") Then fdr(tb & "金额") = 0
    Next
Next

注：应收查询（表A)和应付查询（表C）是由其他表生成的查询表

主题：统计