高手请进(优化代码!!进,销,存系统)

Question

请大家帮我简化一下代码，给出好的算法谢谢！~！！做的是一个进，销，存 报表。 一共有 五个表, 进货单，销售单，货品库，店名资料 ,退货单
-------------------进货单------------------
货名号，进货方，数量, y1, y2, y3, y4 ,时间  y是规格，数量=sum(y1)合
 001   x1        3     1   1   1   0   04-4-1
 002   x1        5     2    1   1  1    04-4-3
 002   x2        9     2    2   2  3    04-4-8
 002   x4        5     2    1   1  1    04-4-2
 003   x4        8     2     2   2  2    04-6-5-------------------退货单------------------
货名号，退货方，数量, y1, y2, y3, y4 ,时间  
 001   x1        1     0   0   0   1   04-2-1
 001   x2        3     2    0   1  0    04-1-3
 003   x1        1     0    0   1  0    04-3-8
 002   x3        2     0     0  0   2    04-4-25
 003   x4        3     1     1   1  0    04-6-5-------------------销售单------------------ 店名 ,货名号, 数量,金额, y1  y2  y3  y4  时间 x1     002     3    500   1   0  1   1   04-5-6
  
 x1     001     1     230   1   0  0  0   04-4-5
 x2     003      3    620   1   0  2 0    04-2-3 
-------------------货品库------------------ 货名号,  货品名称, 单位,进货价
 001         长库    条    30
 002         短库    条    35
 003         男库    条    10
------------------- 店名资料------------------店名, 店地址, 联系人
x1     连天    张大红 
x2     明天     信笺
x3      ..............
 -------------根据以上的资料得到一个库存报表-----------------------------------
  时间条件:上月为 <data1: 
           本月为>=data1 and <=data2
   上月存=上月进-上月销-上月退
   本月存=上月存+本月进-本月退-本月销
货名号,上月存，本月进,本月退，本月销，本月存, y1,y2,y3,y4
 002    3        5      2       1       5      3  1  1  0
 001    0         2     0        0      2      0   1  1  0
  ..........
  ..........
--------------------------------------------------------------
----------------------------------------------------------------
  我的代码！！很差，。请大家优化！！！Dim s_j As Integer
Dim s_s As Integer  '上月
Dim s_c As Integer
Dim s_t As Integer
Dim b_j As Integer
Dim b_t As Integer  '本月
Dim b_s As Integer
Dim b_c As IntegerDim b_JE As Integer
Dim qm As StringDim dw As String
Dim dh As StringDim y_j(3) As Integer ' 衣服进货的规格
Dim y_s(3) As Integer ' 衣服销货的规格
Dim Y_c(3) As Integer ' 衣服存货的规格
Dim y_t(3) As Integer
Dim y_in(3) As Integer
Dim data1, data2 As String  '时间转换
Dim sql1, sql2, sql3, sql4, sql5, sql6, sql7, sql8, sql9 As StringSet re = New ADODB.RecordsetSet re1 = New ADODB.RecordsetSet re2 = New ADODB.Recordset
Set re3 = New ADODB.Recordset
Set re4 = New ADODB.Recordset
Set re5 = New ADODB.Recordset
Set re6 = New ADODB.Recordset
Set re7 = New ADODB.Recordset
Set re8 = New ADODB.Recordset
Set re9 = New ADODB.Recordset
  
 re.Open "select 货品代号,货品名称,单位 from 货品库 where 货品代号 like '" & sel & "%'  ", con,                                  adOpenDynamic, adLockBatchOptimistic
           For i = 1 To re.RecordCount
     
                   Set re1 = New ADODB.Recordset
                    '上月存
      sql1 = "select sum(数量) from 进货单 where 日期<'" & data1 & "' and 进货方='仓库'                                                and 货号代码='" & re.Fields(0) & "' " '上月进
      sql2 = "select sum(数量) from 出货单 where 日期<'" & data1 & "' and 发货方='仓库'                                                and 货品代码='" & re.Fields(0) & "' " '上月销
      sql3 = "select sum(数量) from 退货单 where 日期<'" & data1 & "' and 退货方='仓库'                                                and 货号代码='" & re.Fields(0) & "' " ' 上月退.退
                    '本月
      sql4 = "select sum(数量) from 进货单 where 日期>='" & data1 & "'and 日期<='" & data2                              & "'and 进货方='仓库' and 货号代码='" & re.Fields(0) & "' " '本月进
      sql5 = "select sum(数量),sum(金额) from 出货单 where 日期>='" & data1 & "'and 日期                       <='" & data2 & "'and 发货方='仓库'  and 货品代码='" & re.Fields(0) & "' " '本月销
      sql6 = "select sum(数量) from 退货单 where 日期>='" & data1 & "' and 日期<='" &                          data2 & "' and 退货方='仓库'  and 货号代码='" & re.Fields(0) & "' " ' 本月月退.退
      
     sql7 = "select sum(Y1),sum(Y2),sum(Y3),sum(Y4), from 进货单 where 日期<='" & data2 & "' and                     货号代码='" & re.Fields(0) & "' and 进货方='仓库'" 
                  '所有小于日期的进货
   sql8 = "select sum(Y1),sum(Y2),sum(Y3),sum(Y4)from 出货单  where 货品代码='" & re.Fields(0)                      & "'and 日期<='" & data2 & "'and 发货方='仓库'" 
                  '所有小于日期的出货
   sql9 = "select sum(y1),sum(Y2),sum(Y3),sum(Y4) from 退货单 where 货号代码='" & re.Fields(0)                        & "'and 日期<='" & data2 & "'and 退货方='仓库'" 
                  '所有小于日期的退货.退
  
      re1.Open sql1, con, adOpenDynamic, adLockBatchOptimistic
      re2.Open sql2, con, adOpenDynamic, adLockBatchOptimistic
      re3.Open sql3, con, adOpenDynamic, adLockBatchOptimistic
      re4.Open sql4, con, adOpenDynamic, adLockBatchOptimistic
      re5.Open sql5, con, adOpenDynamic, adLockBatchOptimistic
      re6.Open sql6, con, adOpenDynamic, adLockBatchOptimistic
      re7.Open sql7, con, adOpenDynamic, adLockBatchOptimistic
      re8.Open sql8, con, adOpenDynamic, adLockBatchOptimistic
      re9.Open sql9, con, adOpenDynamic, adLockBatchOptimistic
     
       On Error Resume Next
        s_j = 0
        s_s = 0
        s_t = 0
        '本月
         
        b_j = 0  '上月
        b_s = 0
        b_t = 0
       
        s_c = 0
        b_JE = 0
        
        s_j = re1.Fields(0).Value
        s_s = re2.Fields(0).Value
        s_t = re3.Fields(0).Value
       
        
        b_j = re4.Fields(0).Value
        b_s = re5.Fields(0).Value
        b_JE = re5.Fields(1).Value
        b_t = re6.Fields(0).Value
       
        s_c = s_j - s_s - s_t         If s_s > s_j Then
             s_c = 0
        End If
             b_c = s_c + b_j - b_s - b_t 
             qm = ""
             dw = ""
             dh = ""
             qm = re.Fields(0).Value
             dh = re.Fields(1).Value
             dw = re.Fields(2).Value
   
            
         For m = 0 To 3
          y_j(m) = 0
          y_s(m) = 0
          y_t(m) = 0
          Y_c(m) = 0
          y_in(m) = 0
          y_j(m) = re7.Fields(m).Value
          y_s(m) = re8.Fields(m).Value
          y_t(m) = re9.Fields(m).Value
        
          Y_c(m) = y_j(m) - y_s(m) - y_t(m) 
         Next
         
   sql = "insert into temp库存表 (品名,货号,金额,上月存,本月进,本月退,本月销,本月存,y1,y2,y3,y4) values('" & dh & "','" & qm & "'," & b_JE & "," & s_c & "," & b_j & "," & b_t & "," & b_s & "," & b_c & "," & Y_c(0) & "," & Y_c(1) & "," & Y_c(2) & "," & Y_c(3) & ")"
  
    con.Execute sql
        re1.Close
        re2.Close
        re3.Close
        re4.Close
        re5.Close
        re6.Close
        re7.Close
        re8.Close
        re9.Close
       
        
        re.MoveNext
        
  Next

Answer 1

把‘货品库 ’表作为基本表，通过‘货品代号’把其它表join到一起，这样就可以通过一条sql一次性的把要的dh，qm，b_JE等等取出来，这样代码或许会少些，

Answer 2

re1.Open sql1, con, adOpenDynamic, adLockBatchOptimistic
      re2.Open sql2, con, adOpenDynamic, adLockBatchOptimistic
      re3.Open sql3, con, adOpenDynamic, adLockBatchOptimistic
      re4.Open sql4, con, adOpenDynamic, adLockBatchOptimistic
      re5.Open sql5, con, adOpenDynamic, adLockBatchOptimistic
      re6.Open sql6, con, adOpenDynamic, adLockBatchOptimistic
      re7.Open sql7, con, adOpenDynamic, adLockBatchOptimistic
      re8.Open sql8, con, adOpenDynamic, adLockBatchOptimistic
      re9.Open sql9, con, adOpenDynamic, adLockBatchOptimistic
这里你可以写个函数到模块中：
如：Public Function SetSql(ByVal sqlstr As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\database\abank.mdb;Persist Security Info=False"
con.CursorLocation = adUseClient
con.Open str
If rs.State <> adStateClosed Then
rs.Close
End If
rs.Open sqlstr, con, adOpenStatic, adLockOptimistic
Set SetSql = rs
End Function
这个函数仅供参考，里面的数据库关联需要结合你的系统
而你在使用如上这么9句时就可以
这样调用：
set re1=Setsql(sql1)
set re2=Setsql(sql2)
.
.
.
这样一旦有问题你也方便查找修改

Answer 3

其实优化代码不重要！！我是要优化速度！！因为当库存表中的货品多了的话！！（》10）
生成报表就的30多秒！！
原因是 set re. open re这些是很浪费资源的1！！！
不知有没有好的方法！！我希望 insert into 直接添加！不现查，在复制！！，这些过程在insert into 写！！也许不可能！！
sql = "insert into temp库存表 (品名,货号,金额,上月存,本月进,本月退,本月销,本月存,y1,y2,y3,y4) values('" & dh & "','" & qm & "'," & b_JE & "," & s_c & "," & b_j & "," & b_t & "," & b_s & "," & b_c & "," & Y_c(0) & "," & Y_c(1) & "," & Y_c(2) & "," & Y_c(3) & ")"

Answer 4

为什么不能少“库存表”？库存数可以动态计算的
如果只有单独的库存数量字段, 有可能会因为程序的bug或者意外情况造成从库存数量读出的值与实际计算作的值不同,数据不一致

Answer 5

大家对 “库存表” 有些疑问！！ 我觉得不管怎么做“库存表”都是通过计蒜得到呀！！也免不了上面的代码！！
有的朋友说用存储过程和视图
说实话我对存储过程和视图 不是太了解！！
我用的是access 数据库！！不知是否完全支持！！
请此代码！！

Answer 6

有一个非常有效的方法：
　　insert int 库存表
    select * from　进货表

Answer 7

大家多我的库存表还是不了解，不是简单的一个查询后生成的！
 xmczm(高仁) 所说的 insert int 库存表
                   select * from　进货表
                 原不止那么简单！！ 
                                   上月存=上月进-上月销-上月退
                                   本月存=上月存+本月进-本月退-本月销
                                    时间条件:上月为 <data1: 
                                             本月为>=data1 and <=data2 其中上月进-上月销-上月退，上月存+本月进-本月退-本月销
都需要查询得到的！！这个“库存表”需要看的不紧当前的库存，有时看看上月，等！！

Answer 8

表太多了  ..............不过按照我的感觉是不许要那么多了 反正我是看晕掉了   要你改表结构 估计你不愿意改了 
我建议你要充分利用 利用GROUP BY语句 比如 ： 
sql9 = "select 规格，sum(y1),sum(Y2),sum(Y3),sum(Y4) from 退货单 where 货号代码='" & re.Fields(0)    & "'and 日期<='" & data2 & "'and 退货方='仓库'  group by 规格" 
 
那你需要执行的ＳＱＬ语句就少了很多了　　速度的提高　那是最明显的

Answer 9

前面我写的语句应该改成 sql9 = "select 货号代码，sum(y1),sum(Y2),sum(Y3),sum(Y4) from 退货单 where  日期<='" & data2 & "'and 退货方='仓库'  group by 货号代码" 很多代码你就可以在返回的值里面写了  我以前写代码也和你 一样的，  慢慢来，  以后你的代码 和数据库会定得更合理的了