Random numbers

[Jeanne Conroy]

I am an instructor with a series of test questions. I want to randomize the
way the questions appear on a form for the student's test. I tried using a
query and adding a field that generated a random number but my Access
version (2000) apparently does not support it. (I get an unknown function
error message.)

How can I randomize this set of questions?

JC

 

[Allen Browne]

Try a query like this:
SELECT TOP 20 tblQuestion.*
FROM tblQuestion
ORDER BY Rnd(tblQuestion.QuestionID);

That selects 20 random questions from a table named tblQuestion that has a
primary key named QuestionID.

Notes:
1. The Rnd() function is built-in. If it is not recognised, you have a
References problem. See:
http://members.iinet.net.au/~allenbrowne/ser-38.html

2. You must issue a Randomize in your session for this to be random.

3. The Rnd() function does not do anything with the QuestionID, but if you
do not include it the query optimiser does not call the function on each
row.

 

[John Vinson]

I am an instructor with a series of test questions. I want to randomize the
way the questions appear on a form for the student's test. I tried using a
query and adding a field that generated a random number but my Access
version (2000) apparently does not support it. (I get an unknown function
error message.)

How can I randomize this set of questions?

JC

There's an Access VBA function Rnd() - but it's a bit tricky to use in
a Query. Unless you pass a table field as a parameter to a function,
Access will "save time" by calling the function only once.

To sort the results of a query in random order, you can use the Top
Values property of a query, with help from a little VBA. Put this
little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.


John W. Vinson[MVP]