Random Selection

[Gootz11]

Hello All
I need to run a query that looks at the values on a table and randomly selects 87 records.
Thanks for your help
Jaime

 

[Jon K]

Try this query (type in the SQL View of a new query, using the correct table name and a field name):-

SELECT TOP 87 *
FROM [TableName]
ORDER BY Rnd([NameOfAnyNumericField]);


If there is no numeric field in the table, you can use a text field instead:-

ORDER BY Rnd(Asc([NameOfAnyTextField]));

 

[Gootz11]

I tried your SQL but it didn't work, got an error meassage of "invalid procedure code"
Let me give you a better idea of what I am working with,
My table call NESKUS has over 20000 records, each record has a slot assign to it. (exmp of slot: R 52911, R 53011...) I need my query to look at my slot column and ramdomly choose 87 records.
Thanks Again
JG

 

[Jon K]

In the attached DB, I created a table NESKUS and the following query:

query_Original:-
SELECT TOP 87 *
FROM NESKUS
ORDER BY Rnd(Asc([Slot]));

The query ran without error on my system. However, I find that every time the DB is opened and the query is run, it returns the same 87 random records. I think it is because every time the DB is opened, the Rnd() function starts with the same seed.


I have revised the query. Now the Rnd() function uses "the last five digits of the Slot minus the figure returned by the Timer() function" as the seed, so that every time the DB is opened, the Rnd() function starts with a different seed. And so different 87 random records will be returned every time the DB is opened.

query_Revised:-
SELECT TOP 87 *
FROM NESKUS
ORDER BY Rnd(Right([Slot],5)-Timer());

You can run this revised query in the attached DB to see if it works on you system.


Note: The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save with a new name when the DB is opened for the first time.

 

[Mile-O]

I did something like this once whereby I had to select 5 random records from a table. Rather than do it in a query I performed this in code, first selecting the five random records and then making a table to store them.

Here's the code I used to select the random records.

 Sub RandomPick(ByRef intRecords As Integer)
 
    On Error GoTo Err_RandomPick
 
    ' Author: Mile-O-Phile
    ' History: 07-Nov-2002 - Initially written
    ' Discussion: Selects five random numbers without duplication
    '             In order to select five random records from a recordset, we must first decide which
    '             records we are going to take from the recordset.

    Dim intCounterA As Integer, intCounterB As Integer ' Loop counters
    Dim intRandom(1 To 5) As Integer ' Store for accepted numbers
    Dim intCurrent As Integer ' The current random number
    Dim intPosition As Integer ' Holds our position in the array
    Dim booUnusable As Boolean ' Determines whether number is a duplicate
    
    intPosition = 1

    For intCounterA = 1 To 5
        ' This ensures we get a random number within our given range
        intCurrent = Int(Rnd() * intRecords) + 1
        
        ' Next, we must check that the number we have just selected doesn't match any others
        For intCounterB = 1 To 5
            If (intCurrent = intRandom(intCounterB)) Then
                booUnusable = True
                Exit For
            End If
            
            If (intRandom(intCounterB) = 0) Then
                ' There will only be a zero value in the array if no value has been entered
                ' in intRandom(?) for that index so it can be safely left if a zero is seen
                Exit For
            End If
        Next intCounterB ' return the counter
        
        While booUnusable
            ' If the number generated is a duplicate then the routine should continue generating random
            ' numbers until there is a useable value
            booUnusable = False
            intCurrent = Int(Rnd() * intRecords) + 1
            For intCounterB = 1 To 5
                ' Again, the number must be checked to determine whether it is a duplicate
                If (intCurrent = intRandom(intCounterB)) Then
                    booUnusable = True
                    Exit For
                End If
            Next intCounterB ' return the counter
        Wend
        ' If a useable number is achieved then the search for that array index is considered to be complete
        intRandom(intPosition) = intCurrent
        intPosition = intPosition + 1 ' increment the array index
    Next intCounterA ' return the counter
    
    Call MakeRandomTable(intRandom())
    
Exit_RandomPick:
    Exit Sub
    
Err_RandomPick:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in Sub RandomPick"
    Resume Exit_RandomPick
    
End Sub

Sub MakeRandomTable(ByRef intRandom() As Integer)

    On Error GoTo Err_MakeRandomTable

    ' Author: Mile-O-Phile
    ' History: 07-Nov-2002 - Initially written
    ' Discussion: Writes the five randomly selected records into a temporary table
    
    Dim db As Database ' database object
    Dim rsNew As Recordset, rsQuery As Recordset ' recordset objects
    Dim intCounterA As Integer, intCounterB As Integer ' Loop counters
    
    Set db = CurrentDb
    Set rsNew = db.OpenRecordset("tblRandom")
    Set rsQuery = db.OpenRecordset("qryRandomReport")
    
    If rsNew.RecordCount = 0 Then ' If the table si empty then there is no need to delete records
        ' do nothing
    Else
        rsNew.MoveFirst
        For intCounterA = 1 To rsNew.RecordCount
            With rsNew
                .Delete
                .MoveNext
            End With
        Next intCounterA
    End If
    
    For intCounterA = 1 To DCount("[Date]", "qryRandomReport")
        For intCounterB = 1 To 5
            If intCounterA = intRandom(intCounterB) Then
                With rsNew
                    .AddNew
                    .Fields("ID") = rsQuery.Fields("ID")
                     ' ***** all all other fields were appended here
                     ' an UPDATE query may be simpler to transfer the records individually
                    .Update
                End With
            End If
        Next intCounterB
        rsQuery.MoveNext
    Next intCounterA
    
Exit_MakeRandomTable:
    Exit Sub
    
Err_MakeRandomTable:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in Sub MakeRandomTable"
    Resume Exit_MakeRandomTable
        
End Sub

At least this I would run the function which would create a table with the random records I wanted and then had a table I could use as a RecordSource for reviewing them.

 

[Gootz11]

Thanks to both of you for responding, the query worked perfectly.
I'll give the code a try tomorrow, i been trying to teach myself VBA and this might be a good one to start with.
thanks again
JG

 

[Jon K]

On further testing, I find that my way of using Right([Slot],5)-Timer() to start a different seed fails when the query is run in the hours around noon.

Maybe using VBA code is the better idea, as you can use the Randomize statement in code to initialize the random-number generator.