Random but not repeating - a better solution?

[ChipperT]

I am creating a quiz program from young people based on academic Knowledge Bowl rules. Currently the "universe" of questions numbers just under 3,000 and are stored in an Access 2007 database. From this set of questions, each match will consist of x number of questions (standard is 30 but that is variable depending on the options that the quizmaster controls). Each question has an "index" to identify that question. I was using the following code to insure that no question repeated in a match:

rsCount = rs.RecordCount
numQuestions = 30 'this number can change depending on the quiz set up options
redim arrQuestionIndex(numQuestions)

Randomize

for i = 0 to numQuestions
rI = 1 + Int((rsCount) - 1) * Rnd())
for j = 0 to ubound(arrQuestionIndex) -1
if rI=arrQuestionIndex(j) Then
i=i-1
Exit For
End If
Next
arrQuestionIndex(i) = rI
Next

That worked well and no question repeated. However, there are many matches in a tournament and I found that in subsequent matches a high percentage of the questions would be repeated from the match before. Unacceptable! I tried building an iniitial array based on the code above containing the indexes of all questions in the question set from which I would then use index + numQuestions for a match, noving the index down each time until I ran out of questions in the initial array, at which time I would re-randomize the question set and rebuild the array. The problem with that is that it would take a long, long time to build that array with that many interations through the loop!

My next idea was the have a temp array (arrTemp) of question indexes pulled from a table query with rs.GetRows. I would then randomize and select a random index as above. I would then move the question index from tempArray to arrQuestionIndex and rebuild the tempArray without the selected cell. That made the task pretty fast since each time tempArray was one smaller. But it seems to be very messy!

Just curious if anyone else has a more elegant solution for this problem.

 

[The_Doc_Man]

The last time I had to do something like this, it was shuffling a deck of cards, but the idea might transfer OK. You are building a relatively short target table, so I might try this...

Have a "targeted questions" table that lists the index of the questions you want to see. The ONLY FIELD in the table is the targeted index. Erase the table's contents ("DELETE * FROM MyShortIndexList"). Now use your random number generator to create a number in the range of available questions. Something like....

X = CLng( Rnd() * Indexrange ) + 1

Before you store this number, execute a DCount of the MyShortIndexList table to get a count of records where the index field's value = X. This count will be zero or not zero. If it is zero, you can do a recordset .AddNew for that record number (and do the .Update afterwards, of course.) If it is not zero, select another X.

As you store records, count how many you have stored. When you reach the size of the list in question, you are done. (Always remember to close what you opened.)

To get the questions out of the question table, you can do a query based on ... "WHERE MyIndex IN (SELECT Xlist FROM MyShortIndexTable)"

This should be a VERY short list. Now, if you need to remember that list but build another one, the short index table just got bigger because you have to remember test numbers or something similar as a non-unique key. Even so, the search and short list will be fast because 30 records is chump change to do DCounts and such.

 

[boblarson]

I would build a table for holding used question id's. Then you can use that table to exclude data from the query. Check the random numbers to make sure that it exists in the current query and if not have it choose another.

Then you can have a reset button which will clear the used questions table so you can start all over. Here's how you can use a table to exclude data in a query.

 

[ChipperT]

I like your solution, Bob. Quick and avoids all that looping. Thanks also, Doc Man