Office中国论坛/Access中国论坛

标题: 请教andymark:以下这段代码如何合并？ [打印本页]

作者: cyber-bobo 时间: 2006-10-21 05:47
标题: 请教andymark:以下这段代码如何合并？
Private Sub 命令1_Click()
Dim Rs5 As New ADODB.Recordset
Dim Rst As New ADODB.Recordset
Dim Rs6 As New ADODB.Recordset
Dim Rst1 As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim I As Integer
Dim J As Integer
Dim K As Integer
Set Conn = CurrentProject.Connection

Rst.Open "SELECT 产品ID,Count(产品ID) AS 产品ID之计数 FROM 临时表 GROUP BY 产品ID ORDER BY Count(产品ID) DESC , 产品ID", Conn, adOpenDynamic, adLockOptimistic

Do While Not Rst.EOF
  I = I + 1
Rs5.Open "select * from 临时表 where 产品ID='" & Rst.Fields(0) & "' And 要求到货日期<# " & Year(Date) & "-" & Month(Date) & "-26# order by 产品ID", Conn, adOpenDynamic, adLockOptimistic

J = 0

Do While Not Rs5.EOF
J = J + 1
K = Int((I - 0.1) / 11) + 1

Rs5.Fields("打印ID") = K
Rs5.MoveNext

If J Mod 6 = 0 Then
   I = I + 1
Else
   I = I
End If

  Loop

  Rs5.Close
  Rst.MoveNext

Loop
'----------------------------------------------

Rst1.Open "SELECT 产品ID,Count(产品ID) AS 产品ID之计数 FROM 临时表 GROUP BY 产品ID ORDER BY Count(产品ID) DESC , 产品ID", Conn, adOpenDynamic, adLockOptimistic

Do While Not Rst1.EOF
  I = I + 1
  Rs6.Open "select * from 临时表 where 产品ID='" & Rst1.Fields(0) & "' And 要求到货日期># " & Year(Date) & "-" & Month(Date) & "-25# order by 产品ID", Conn, adOpenDynamic, adLockOptimistic

J = 0

  Do While Not Rs6.EOF
J = J + 1
K = Int((I - 0.1) / 11) + 1

Rs6.Fields("打印ID") = K
Rs6.MoveNext

If J Mod 6 = 0 Then
      I = I + 1
   Else
      I = I
End If

  Loop

  Rs6.Close
  Rst1.MoveNext

Loop

Set Rs5 = Nothing
Set Rst = Nothing
Set Rs6 = Nothing
Set Rst1 = Nothing
Set Conn = Nothing

End Sub

实际上是以本月25日为分界，分开两部份来排序，但排序号要连续不归零。

作者: andymark 时间: 2006-10-21 05:59
在第二段代码开头增加一句

I=K

作者: cyber-bobo 时间: 2006-10-21 06:44
不对

作者: andymark 时间: 2006-10-21 07:05
把例子传上来

作者: cyber-bobo 时间: 2006-10-21 07:17
[attach]21076[/attach]
要求：按本月25日为分界分为两部份，每5条记录更换一次ID号，产品相同的每3条记录当一条记录处理

作者: andymark 时间: 2006-10-21 07:45
排序统计要不要按日期段分开

Rst.Open "SELECT 产品ID,Count(产品ID) AS 产品ID之计数 FROM 临时表 where 要求到货日期<# " & Year(Date) & "-" & Month(Date) & "-26# GROUP BY 产品ID ORDER BY Count(产品ID) DESC , 产品ID", Conn, adOpenDynamic, adLockOptimistic

作者: cyber-bobo 时间: 2006-10-21 07:49
想要的结果：

[attach]21078[/attach]

分开两部份后，排序要连续，例如25日前最后的排序号为5,那么25日后的排序就由6开始。

作者: cyber-bobo 时间: 2006-10-21 09:40
深夜研究，终于搞掂，在此谢过！

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)