技術(shù) 點
- 技術(shù)
- 點
- V幣
- 點
- 積分
- 21536
|
4#
發(fā)表于 2011-3-9 08:30:56
|
只看該作者
roych 發(fā)表于 2011-3-9 00:37
俺的想法是,先隨機抽取5項20%的數(shù)據(jù),再據(jù)此做聯(lián)合查詢,從而得到每項均20%的數(shù)據(jù)……
或許子查詢也是個 ...
Sub 抽樣查詢()
Dim ssql As String
Dim n As Long
Dim i As Long
Dim j As Long
Dim str As String
Dim strwh As String
If IsNull(Me.年度.Value) = False And IsNull(Me.月度.Value) = False And IsNull(Me.抽樣比率.Value) = False Then
ssql = "DELETE * from 臨時表"
CurrentDb.Execute ssql
str = "ID, 流水序列號, 產(chǎn)品單號, 客戶姓名, 預(yù)估金額, 給付方式, 受理類型, 受理員工號, 申請時間"
For i = 0 To Me.受理類型.ListCount - 1
For j = 0 To Me.受理員工號.ListCount - 1
strwh = "year(申請時間)=" & Me.年度.Value
strwh = strwh & " and month(申請時間)=" & Me.月度.Value
strwh = strwh & " and 受理類型='" & Me.受理類型.Column(0, i) & "'"
strwh = strwh & " and 受理員工號='" & Me.受理員工號.Column(0, j) & "'"
n = Round(DCount("*", "日常數(shù)據(jù)", strwh) * Me.抽樣比率.Value, 0)
If n > 0 Then
ssql = "INSERT INTO 臨時表 ( " & str & " ) "
ssql = ssql & "SELECT TOP " & n & " " & str
ssql = ssql & " FROM 日常數(shù)據(jù) "
ssql = ssql & "WHERE " & strwh
ssql = ssql & " ORDER BY Rnd([ID])"
CurrentDb.Execute ssql
End If
Next
Next
Else
MsgBox "請選擇抽樣查詢年度、月度、抽樣比率!"
End If
End Sub
如果對數(shù)量極少的抽樣樣本,可以考慮將n設(shè)置為如下:
n = Int(DCount("*", "日常數(shù)據(jù)", strwh) * Me.抽樣比率.Value) - (DCount("*", "日常數(shù)據(jù)", strwh) * Me.抽樣比率.Value - Int(DCount("*", "日常數(shù)據(jù)", strwh) * Me.抽樣比率.Value) > 0)
這樣可以保證最少抽到一條記錄。 |
|