General

6.12 Randomly Selected Unique Records.
  Farid Ahmad of Islamabad PK.
For a test-taking application, I needed to select a random set of questions from a Access 97 database table. At the same time I had to ensure that a question did not appear twice. The following function provides this facility. It expects a table called "Questions" with a field called "Qnum" containing a unique question number for each record. The function also expects the table to be open in a recordset called "rst".

The function parameter "total" indicates the number of questions to be selected. Note that it is the calling procedure's responsibility to ensure that the table "Questions" contains at least this many records - if "total" is greater that the number of records in "Questions", the function will get into an infinite loop. Of course it is easy enough to code an appropriate check for this, but I want to keep the function simple for this example.

When the function is done, it returns a string which contains the numbers of the selected questions separated by commas, e.g. "1,34,23,45" Now this string can be used in an SQL statement of the type:

SELECT * from questions WHERE QNum IN(" & questionsString & ")
Function getRand(total As Integer) As String
   'Function assumes that a recordset called rst is open
   Dim collected As Integer 'keep track of the number of records selected
   Dim RandNum As Integer
   Dim questionNum As Integer 'will contain Qnum of selected record
   Dim count As Integer 'count number of records in rst
   Dim fill As String 'results are filled into this
   fill = ","
   rst.MoveLast 'so that RecordCount function works right
   count = rst.RecordCount - 1
   collected = 0
   Do
      RandNum = Int((count * Rnd))
      rst.MoveFirst
      rst.Move (RandNum) 'move to the selected record
      questionNum = rst![qnum] 'read Qnum
      If InStr(fill, "," & Str(questionNum) & ",") = 0 Then
         'if its not there already
         fill = fill & Str(questionNum) & ","
         'then add it
         collected = collected + 1
      End If
   Loop Until collected = total
   fill = Left(fill, Len(fill) - 1) 'get rid of , from the end
   fill = Right(fill, Len(fill) - 1) 'get rid of , from the start
   getRand = fill
End Function

Nota di Giorgio Rancati
Come scritto nelle sue note, la funzione di cui sopra presuppone che il recordset risulti già aperto.
E' anche da ricordare che quando si apre Access il generatore dei numeri casuali ha sempre lo stesso valore, quindi si rischia che la funzione restituisca come primo record sempre lo stesso.
Per evitare questo inconveniente è necessario prima di richiamare la funzione, oltre ad aprire il recordset, usare anche l'istruzione Randomize (vedi help in linea di Access).


Se pensate di avere del materiale freeware interessante e volete pubblicarlo, allora leggete qui.