Random Sample 1

[pleasehelpalot]

I need a random sample of ten names from a table with 139 names. The code I currently am using is not returning all the names and repeats the names it picks in an uneven occurance. One name may be picked one time and another hundreds of times. I run one thousand sets of ten to test the result so it's a good size sampling. I don't think the sample seed is being randomized with this code.
Here is the code:

Initialize ASP RND() Function
Function Random() As String

Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oRS1 As ADODB.Recordset
Dim oRS2 As ADODB.Recordset
Dim sSql As String
Dim sSQL1 As String
Dim sSQL2 As String
Dim randNum As Integer
Dim sList As String


Const Conn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:/local Mediation/NewMediationBackEnd.mdb"
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
Set oRS1 = New ADODB.Recordset
Set oRS2 = New ADODB.Recordset

oConn.Open Conn
Dim upperBound
Dim lowerBound


upperBound = 139
lowerBound = 1
Debug.Print upperBound
Debug.Print lowerBound

Dim s As String
For j = 0 To 19 Step 1
randNum = Int((upperBound - lowerBound + 1) * Rnd + 1)


s = s & randNum
If (j < 19) Then

s = s & ","
End If
Next j
Debug.Print s

strSQL = "select top 10 ArbitersID,fname+' '+ lName from Arbiters where ArbitersID in (" & s & ") and lname not like '%Hold%'"

oRS1.Open strSQL, oConn

strSQL1 = "Select * from RandomNumberTemptbl "
strsql2 = "Select * from RandomNumberPermanenttbl "

oRS.LockType = adLockOptimistic
oRS2.LockType = adLockOptimistic

oRS.Open strSQL1, oConn
oRS2.Open strsql2, oConn

While Not oRS1.EOF
With oRS
.AddNew
!FullName = oRS1.Fields(1)
.Update
End With

With oRS2
.AddNew
!FullName = oRS1.Fields(1)
.Update
End With

Debug.Print oRS1.Fields(0), oRS1.Fields(1)
oRS1.MoveNext
Wend

End Function

There is a lot going on here and I'm struggling to understand how to make it work. It ultimatley puts the result in a temporary table from which a report is created. Most of this was written by programmers with more experience than I have. Can anyone help with this?

 

[PHV]

Have you tried something like this instead ?
strSQL = "SELECT TOP 10 ArbitersID,fname & ' ' & lName FROM Arbiters WHERE lname Not Like '%Hold%' ORDER BY Rnd(ArbitersID)"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pleasehelpalot]

I'll give your suggestion a try in the morning and let you know the outcome.
Thanks

 

[pleasehelpalot]

strSQL = "SELECT TOP 10 ArbitersID,fname & ' ' & lName FROM Arbiters WHERE lname Not Like '%Hold%' ORDER BY Rnd(ArbitersID)"


This code resulted in the same 10 names being picked every time.

 

[pleasehelpalot]

Sorry, I left the Rnd off when I ran the code. With the Order by Rnd I got the results I needed. All names where sampled and in a random order.
Thanks PHV for your help. I've been at this for a few weeks
and you gave me the solution in no time at all.