主题：汇总求助

难道每个产品，每次入库的单价都相同？ 应该是变化的吧？

如果是我，我会改变设计思路，规范仓库的入库表和出库表建立关联，入库表是父表，出库表是子表，每次出库选择该产品的未出库完成的某个入库单，在对应的关联表（入库.出库）中录入出库资料，如果这个入库单的数量少于这次出库，多出的数量再找下一个入库单录入，这样入库金额、出库金额、某个入库单已经出库的数量、剩余数量、何时出路、每次出库多少、是否出库完毕，都清清楚楚。

我在7楼回复的方法是最合理的，得出的结果是最精确的，也可以大大规范你们的仓库管理。

我不是万能的，特别是涉及具体业务的时候，我又不懂。

看13楼的回复。

嘿嘿，楼主很幸运，遇到c版

效率问题，看看：

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

 

输入3000行数据，给大家测试

应该是这个原因：

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

 

 

我没有测试，你自己测试下面的代码：

 

DataTables("月报表").DataRows.Clear
Dim s As String = e.Form.controls("月份").Value
Tables("月报表").StopRedraw
If s <> "" Then
    Dim n As Integer = e.Form.controls("月份").Value
    DataTables("月报表").DataRows.Clear
    Dim Arys As List(Of String())
    Arys = DataTables("入仓").GetUniqueValues("", "型号","规格")
   
    Dim drs As New List(of DataRow)
    For Each Ary As String() In Arys
        Dim r As DataRow = DataTables("月报表").AddNew
        r("型号") = Ary(0)
        r("规格") = Ary(1)
        drs.add(r)
    Next
    For Each dr As DataRow In drs
        Dim s1 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 = '" & s & "'"
        Dim s2 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 < " & n
        Dim Sum,Sum1 As Double
        dr("本月入仓_数量") = DataTables("入仓").Compute("Sum(入仓数量)",s1)
        dr("本月入仓_金额") = DataTables("入仓").Compute("Sum(入仓金额)",s1)
        dr("本月出仓_数量") = DataTables("出仓").Compute("Sum(出仓数量)",s1)
       
        Sum = DataTables("入仓").Compute("Sum(入仓金额)",s2)
        Sum1 = DataTables("入仓").Compute("Sum(入仓数量)",s2)
        dr("上月结存_数量") = Sum1 - DataTables("出仓").Compute("Sum(出仓数量)",s2)
       
        '-----------------------计算上月结存金额
        dr("上月结存_金额") = Sum / Sum1 * dr("上月结存_数量")
        '-----------------------
       
        dr("本月加权平均单价") = (dr("上月结存_金额") + dr("本月入仓_金额"))/(dr("上月结存_数量") + dr("本月入仓_数量"))
        dr("本月结存_数量") = dr("上月结存_数量") + dr("本月入仓_数量") - dr("本月出仓_数量")
       
        dr("本月结存_金额") = dr("本月加权平均单价") * dr("本月结存_数量")
        dr("本月出仓_金额") = dr("本月加权平均单价") * dr("本月出仓_数量")
    Next
End If
Tables("月报表").ResumeRedraw

上面的不行，这次应该可以了：

 

DataTables("月报表").DataRows.Clear
Dim s As String = e.Form.Controls("月份").Value
If s <> "" Then
    Dim n As Integer = e.Form.Controls("月份").Value
    DataTables("月报表").DataRows.Clear
    Dim Arys As List(Of String())
    Arys = DataTables("入仓").GetUniqueValues("", "型号","规格")
    For Each Ary As String() In Arys
        Dim r As Row = Tables("月报表").AddNew
        r("型号") = Ary(0)
        r("规格") = Ary(1)
    Next
    Dim drs As new Dictionary(of DataRow,String())
    For Each dr As DataRow In DataTables("月报表").DataRows
        Dim vals(8) As String
        Dim s1 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 = '" & s & "'"
        Dim s2 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 < " & n
        Dim Sum,Sum1 As Double
        vals(0) = DataTables("入仓").Compute("Sum(入仓数量)",s1)
        vals(1) = DataTables("入仓").Compute("Sum(入仓金额)",s1)
        vals(2) = DataTables("出仓").Compute("Sum(出仓数量)",s1)
       
        Sum = DataTables("入仓").Compute("Sum(入仓金额)",s2)
        Sum1 = DataTables("入仓").Compute("Sum(入仓数量)",s2)
        vals(3) = Sum1 - DataTables("出仓").Compute("Sum(出仓数量)",s2)
       
        '-----------------------计算上月结存金额
        vals(4) = Sum / Sum1 * dr("上月结存_数量")
        '-----------------------
       
        vals(5) = (dr("上月结存_金额") + dr("本月入仓_金额"))/(dr("上月结存_数量") + dr("本月入仓_数量"))
        vals(6) = dr("上月结存_数量") + dr("本月入仓_数量") - dr("本月出仓_数量")
       
        vals(7) = dr("本月加权平均单价") * dr("本月结存_数量")
        vals(8) = dr("本月加权平均单价") * dr("本月出仓_数量")
        drs.add(dr,vals)
    Next
    For Each dr As DataRow In drs.Keys
        Dim vals As String() = drs(dr)
        dr("本月入仓_数量") =vals(0)
        dr("本月入仓_金额") = vals(1)
        dr("本月出仓_数量") = vals(2)
        dr("上月结存_数量") = vals(3)
        dr("上月结存_金额") = vals(4)
        dr("本月加权平均单价") = vals(5)
        dr("本月结存_数量") = vals(6)
        dr("本月结存_金额") = vals(7)
        dr("本月出仓_金额") = vals(8)
    Next
End If

看35楼

这样更快：

 

 

DataTables("月报表").DataRows.Clear
Dim s As String = e.Form.Controls("月份").Value
Tables("月报表").StopRedraw
If s <> "" Then
    Dim n As Integer = e.Form.Controls("月份").Value
    DataTables("月报表").DataRows.Clear
    Dim Arys As List(Of String())
    Arys = DataTables("入仓").GetUniqueValues("", "型号","规格")
    For Each Ary As String() In Arys
        Dim r As Row = Tables("月报表").AddNew
        r("型号") = Ary(0)
        r("规格") = Ary(1)
    Next
   
    Dim drs As new Dictionary(of DataRow,String())
    For Each dr As DataRow In DataTables("月报表").DataRows
        Dim vals(8) As String
        Dim s1 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 = '" & s & "'"
        Dim s2 As String = "型号 = '" & dr("型号") & "' And 规格 = '" & dr("规格") & "' And 月份 < " & n
        Dim Sum,Sum1 As Double
        vals(0) = DataTables("入仓").Compute("Sum(入仓数量)",s1)
        vals(1) = DataTables("入仓").Compute("Sum(入仓金额)",s1)
        vals(2) = DataTables("出仓").Compute("Sum(出仓数量)",s1)
       
        Sum = DataTables("入仓").Compute("Sum(入仓金额)",s2)
        Sum1 = DataTables("入仓").Compute("Sum(入仓数量)",s2)
        vals(3) = Sum1 - DataTables("出仓").Compute("Sum(出仓数量)",s2)
       
        '-----------------------计算上月结存金额
        vals(4) = Sum / Sum1 * dr("上月结存_数量")
        '-----------------------
       
        vals(5) = (dr("上月结存_金额") + dr("本月入仓_金额"))/(dr("上月结存_数量") + dr("本月入仓_数量"))
        vals(6) = dr("上月结存_数量") + dr("本月入仓_数量") - dr("本月出仓_数量")
       
        vals(7) = dr("本月加权平均单价") * dr("本月结存_数量")
        vals(8) = dr("本月加权平均单价") * dr("本月出仓_数量")
        drs.add(dr,vals)
    Next
    For Each dr As DataRow In drs.Keys
        Dim vals As String() = drs(dr)
        dr("本月入仓_数量") =vals(0)
        dr("本月入仓_金额") = vals(1)
        dr("本月出仓_数量") = vals(2)
        dr("上月结存_数量") = vals(3)
        dr("上月结存_金额") = vals(4)
        dr("本月加权平均单价") = vals(5)
        dr("本月结存_数量") = vals(6)
        dr("本月结存_金额") = vals(7)
        dr("本月出仓_金额") = vals(8)
    Next
End If
Tables("月报表").ResumeRedraw