求大神帮解决一个统计循环引用变量的方法

sk78911

用我的方法已经可以实现需要的功能，但是不方便后期的维护，且代码太累赘。希望大神能帮简化下，且方便维护1、要对表cx1的数据进行统计，就是统计一个段里面有多少个
       cx1 如图


2、统计结果写入fsd表，如图


3、统计具体要按统计的最高线，最低线，及间隔来进行统计
如图：

我写的代码下：
Private Sub Command0_Click()
If Me.zg <> "" And Me.xx <> "" And Me.bc <> "" Then

SQL = "DELETE fsd.fsd FROM fsd;"
CurrentDb.Execute (SQL)
MsgBox "清除成功！开始统计"
Dim rs As Recordset
Dim k1 As Long
Dim k2 As Long
Dim n As Long
n = Me.bc
zgx = Me.zg
k2 = Me.zg
k1 = k2 - n
Set rs = CurrentDb.OpenRecordset("fsd")
rs.AddNew

For i = Me.zg To Me.xx + n Step -n
n01lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm1'")
n22lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm2'")
n23lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm3'")
n24lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm4'")
n25lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm5'")
n26lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm6'")
n27lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm7'")
n28lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm8'")
n29lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm9'")

rs.AddNew
rs![bm1] = n01lj
rs![bm2] = n22lj
rs![bm3] = n23lj
rs![bm4] = n24lj
rs![bm5] = n25lj
rs![bm6] = n26lj
rs![bm7] = n27lj
rs![bm8] = n28lj
rs![bm9] = n29lj

rs![fsd] = k1 & "—" & k2 - 1
rs.Update
k2 = k1
k1 = k1 - n
Next
MsgBox "统计完成"
rs.Close
Else
MsgBox "请填写完整"
End If
End Sub

4、功能已经能全部实现，但是我想通过循环引用表bmdm里面的部门代码“bm”来简化这不部分代码，
希望bmdm这个表中的bm有增加的时候，只需要在fsd表中增加相应字段就可以完成
n01lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm1'")
n22lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm2'")
n23lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm3'")
n24lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm4'")
n25lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm5'")
n26lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm6'")
n27lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm7'")
n28lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm8'")
n29lj = DCount("*", "cx1", "hczf>=" & k1 & " and hczf<" & k2 & " And bm = 'bm9'")

rs.AddNew
rs![bm1] = n01lj
rs![bm2] = n22lj
rs![bm3] = n23lj
rs![bm4] = n24lj
rs![bm5] = n25lj
rs![bm6] = n26lj
rs![bm7] = n27lj
rs![bm8] = n28lj
rs![bm9] = n29lj

rs![fsd] = k1 & "—" & k2 - 1
rs.Update

5、附件在这，希望那个大神帮解决下，拜求
分段统计.rar (37.58 KB, 下载次数: 4)

2018-8-28 23:15 上传

点击文件名下载附件

microyip

用SQL语句，写Insert语句来操作啊，怎么还一个个值的这么添加进去啊？

sk78911

microyip 发表于 2018-8-29 08:29
用SQL语句，写Insert语句来操作啊，怎么还一个个值的这么添加进去啊？

怎么写呀，没试过，好像也不能简化代码

microyip

我也是醉了，会写Delete，不会写Insert

1. Private Sub Command0_Click()
   
2.     Dim rs As Recordset
   
3.     Dim k1 As Long
   
4.     Dim k2 As Long
   
5.     Dim n As Long
   
6.    
   
7.     If Me.zg <> "" And Me.xx <> "" And Me.bc <> "" Then
   
8.         SQL = "DELETE fsd.fsd FROM fsd;"
   
9.         CurrentDb.Execute (SQL)
   
10.         MsgBox "清除成功！开始统计"
    
11.         n = Me.bc
    
12.         zgx = Me.zg
    
13.         k2 = Me.zg
    
14.         k1 = k2 - n
    
15.         For i = Me.zg To Me.xx + n Step -n
    
16.             SQL = "Select '" & k1 & "—" & (k2 - 1) & "' As fsd"
    
17.             For j = 1 To 9
    
18.                 SQL = SQL & ",sum(iif([bm]='bm" & j & "',1,0)) As bm" & j
    
19.             Next
    
20.             SQL = SQL & " From [cx1] Where [hczf]>=" & k1 & " and [hczf]<" & k2
    
21.             SQL = "Insert Into [fsd] " & SQL
    
22.             CurrentDb.Execute SQL
    
23.             k2 = k1
    
24.             k1 = k1 - n
    
25.         Next
    
26.         MsgBox "统计完成"
    
27.     Else
    
28.         MsgBox "请填写完整"
    
29.     End If
    
30. End Sub

复制代码

microyip

附上附件以供参考

sk78911

microyip 发表于 2018-8-29 10:46
附上附件以供参考

谢谢大神，就是要这个效果，呵呵

sk78911

sk78911 发表于 2018-8-30 09:01
谢谢大神，就是要这个效果，呵呵

大神，还有个问题想请教下，在表CX1中要增加一个字段pm，统计hczf 大于等于他本生的个数，就好比hczf中有332这个值，统计大于等于他的个数，要把hczf中每个值大于等于他的个数都统计出来，我试了好多方法，可以实现，但是计算好慢好慢，因为数据可能有几万条