random help

[Guest]

These are my tables

Questions (Q_ID, Subject, Level, Type
Exams (E_ID, Subject
Applicant (A_ID, Level
NumberofQuestions (Subject, Level, #MC, #E

In printing an exam, the number of examination questions depends on the Subject and Level. Some Subjects have two types of exam questions: MC (multiple choice) and E (essay) but most of the Subjects have MC questions only. A user will input the E_ID and then the exam will be generated. The number of questions (for MC questions or both MC and E) for each Subject and Level will be found in the NumberofQuestions table. By using a query, I was able to get the number of MC and E questions for the user-specified E_ID. My problem is that I don’t know how to randomly select these many questions (no duplicates) from the Questions table. I am new at access and I’m afraid I can’t understand the previous postings related to my problem (on randomizing). Thanks in advance

 

[Guest]

I managed to create a query that will select all the questions/records applicable to a particular exam. However I still don't know how to select a random [n] number of questions from the records (result of the select query).

 

[John Vinson]

I managed to create a query that will select all the questions/records applicable to a particular exam. However I still don't know how to select a random [n] number of questions from the records (result of the select query).

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.