Geek asks and answers

In Access, find a random entry (true random)

what I'm trying to do is every time the program opens an image in a form. So, I have a simple table with 2 column identifiers and ImagePath, how can I create code so that a random record (ImagePath), a form load event or something like that is selected? Rnd is not good, because it will be the same image every time you open the database.

Thank!

+3
source share
5 answers

See this article by Susan Harkins at TechRepublic: http://www.techrepublic.com/blog/howdoi/how-do-i-retrieve-a-random-set-of-records-in-microsoft-access/149

In this query, I used its GetRandomValue function, which returns a different entry each time.

SELECT TOP 1 f.id, GetRandomValue(f.id) AS rnd_value
FROM tblFoo AS f
ORDER BY 2;

Function:

Public Function GetRandomValue(fld As Variant)

  Randomize

  GetRandomValue = Rnd(1)

End Function

. . . .

+1

Randomize once - , Rnd. Rnd: " Rnd Randomize , ".

+3

Rnd ?

Option Compare Database
Option Explicit

Sub Test()
    Randomize
    Dim x As Integer
    'Print the first field of a 100 random records
    For x = 0 To 100
        CallRandomRecord
    Next x
End Sub

Sub CallRandomRecord()
    Dim rs As DAO.Recordset
    Dim recordCount As Long
    Dim randomRecord As Long

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
    rs.MoveLast 'To get the count
    rs.MoveFirst
    recordCount = rs.recordCount - 1
    randomRecord = CLng((recordCount) * Rnd)

     rs.Move randomRecord

     Debug.Print "Random Record No:" & randomRecord & "  Field 1:  " & rs.Fields(0)

End Sub
+2

, , . , @ray023 ( ). @ray023 , , . !

. . TimeThem:

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TimeThem()
Const Loops As Integer = 10
Const TblName As String = "Batches"
Const FldName As String = "BatchName"
Const IndexFld As String = "BatchID"
Dim i As Integer, s As Long, dummy As Variant

    s = GetTickCount
    For i = 1 To Loops
        dummy = HarkinsRandom(TblName, FldName)
    Next i
    Debug.Print "Harkins:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = RandomRecord(TblName, FldName)
    Next i
    Debug.Print "RandomRecord:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = RandomRecordWithIndex(TblName, FldName, IndexFld)
    Next i
    Debug.Print "WithIndex:"; GetTickCount - s

    s = GetTickCount
    For i = 1 To Loops
        dummy = CallRandomRecord(TblName, FldName)
    Next i
    Debug.Print "CallRandom:"; GetTickCount - s


End Sub

Function HarkinsRandom(TblName As String, FldName As String)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(" SELECT TOP 1 " & FldName & _
                                     " FROM " & TblName & _
                                     " ORDER BY GetRandomValue(" & FldName & ")", _
                                     dbOpenForwardOnly)
    HarkinsRandom = rs(0)
End Function

Public Function GetRandomValue(fld As Variant)
  Randomize
  GetRandomValue = Rnd(1)
End Function

Function RandomRecord(TblName As String, FldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String

    Randomize
    NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
    RecNum = Int(Rnd() * NumRecs + 1)
    SQL = " SELECT TOP 1 " & FldName & _
          " FROM (" & _
          "  SELECT TOP " & RecNum & " " & FldName & " " & _
          "  FROM " & TblName & _
          "  ORDER BY " & FldName & ")" & _
          " ORDER BY " & FldName & " DESC"
    Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
    RandomRecord = rs(0)
End Function

Function RandomRecordWithIndex(TblName As String, FldName As String, _
                               Optional IndexedFieldName As String)
Dim NumRecs As Long, RecNum As Long
Dim SQL As String, SubSQL As String, rs As DAO.Recordset
Dim IndexFld As String

    Randomize
    NumRecs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TblName, dbOpenForwardOnly)(0)
    RecNum = Int(Rnd() * NumRecs + 1)
    If Len(IndexedFieldName) = 0 Or IndexedFieldName = FldName Then
        SQL = " SELECT TOP 1 " & FldName & _
              " FROM (" & _
              "  SELECT TOP " & RecNum & " " & FldName & " " & _
              "  FROM " & TblName & _
              "  ORDER BY " & FldName & ")" & _
              " ORDER BY " & FldName & " DESC"
    Else
        SQL = " SELECT TOP 1 " & FldName & _
              " FROM (" & _
              "  SELECT TOP " & RecNum & " " & FldName & ", " & IndexedFieldName & _
              "  FROM " & TblName & _
              "  ORDER BY " & IndexedFieldName & ")" & _
              " ORDER BY " & IndexedFieldName & " DESC"

    End If
    Set rs = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)
    RandomRecordWithIndex = rs(0)
End Function

Function CallRandomRecord(TblName As String, FldName As String)
    Dim rs As DAO.Recordset
    Dim recordCount As Long
    Dim RandomRecord As Long

    Set rs = CurrentDb.OpenRecordset("SELECT " & FldName & " FROM " & TblName)
    rs.MoveLast 'To get the count
    rs.MoveFirst
    recordCount = rs.recordCount - 1
    RandomRecord = CLng((recordCount) * Rnd)

     rs.Move RandomRecord

    CallRandomRecord = rs(0)
'     Debug.Print "Random Record No:" & randomRecord & "  Field 1:  " & rs.Fields(0)

End Function

, 50 000 ( Jet, .. .mdb , ):

Harkins: 4461 
RandomRecord: 2528 
WithIndex: 1918 
CallRandom: 172 

Harkins: 4150 
RandomRecord: 2278 
WithIndex: 2043 
CallRandom: 47 

CallRandom: 63 
WithIndex: 2090 
RandomRecord: 2324 
Harkins: 4197 

CallRandom: 46 
WithIndex: 1997 
RandomRecord: 2169 
Harkins: 4150

, , . , , Harkins, @ray023 25 ( 100 ).

, , .

+2

, , , , , - . 100 , , 1 100.

, :

  Round(100 * Rnd(), 0)

... . Autonumber PK, , . , LinkMaster PK .

Randomize(), , Rnd() Immediate, , .

, SQL . , , , , , . , , Autonumber PK . SQL, :

  SELECT Images.ID, Images.Path
  FROM Images 
  WHERE Images.ID = Round(100 * Rnd(), 0)

Obvoiusly, 100 . Randomize(), Round(100 * Rnd(), 0) , Randomize(), Round(100 * Rnd(), 0).

, , , , .

0

More articles:


All Articles