Well, no-one was willing to do my job for me, but thanks to Steve Bousquin,
I searched experts-exchange and found that there's a solution there, or at
least, there is after a bit of fiddling around in VB (great fun, armed only
with online help ...)
The bare bones are attached; basically you just put the following code into
a module and call it from a macro (with a RunCode macro step).
Note this routine writes from an existing table "tmp" (which gets destroyed,
as in: the sample is removed) to an existing table "sample" (emptied,
without prompting, prior to re-use). Both these tables only have one field
("ID NUMBER").
BTW, Does anyone know the VB code for "copy all fields", as in the
(non-acceptable) rstSample.* = .*
This procedure never uses more than samplesize randomisation calls. It is
left as an exercise to implement the non-destructive version where you loop
sequentially all through the data (potentially), including in the sample if
Rnd < r/N, where r is number still needed in the sample, and N is the number
remaining to be randomised. This algorithm is slower, especially so if N >>
r.
If anyone has a problem using/adapting the attached code, would they please
contact me directly: [log in to unmask]
Simon Fear
________________________________________________________________________
Public Function CallSample()
Dim NoInSample As Integer
NoInSample = InputBox("Required sample size", "", 100)
Call Sample(NoInSample)
End Function
Public Sub Sample(NoInSample)
Dim rstSample As Recordset
Dim rstTemp As Recordset
Dim dbs As Database
Dim intCount As Integer
Dim i As Integer
Dim intRecordNo As Integer
Randomize
Set dbs = CurrentDb
dbs.Execute "DELETE * FROM sample", dbFailOnError
Set rstSample = dbs.OpenRecordset("sample")
Set rstTemp = dbs.OpenRecordset("tmp", dbOpenDynaset)
With rstTemp
.MoveLast
intCount = .RecordCount
.MoveFirst
If intCount < NoInSample Then
MsgBox ("Can't do it: tmp table is not large enough")
Exit Sub
End If
For i = 1 To NoInSample
intRecordNo = (Rnd * intCount) = 1
.AbsolutePosition = intRecordNo
rstSample.AddNew
rstSample![ID NUMBER] = ![ID NUMBER]
rstSample.Update
.Delete
intCount = intCount - 1
Next i
End With
End Sub
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|