[AccessD] A2003: Random order

Dear Group,

I'm writing a prototype Access 2003 ADP in preparation for writing a
"practice" VB/ASP/ADO .NET project, and I wonder if you lot could help me
out with the logic.

It's a survey of 100 questions. There are 10 groups of 10 questions each.
Each group of questions deals with a particular topic. Rather than have the
survey takers see the questions grouped by topic so that they might "steer"
the results, I thought it would be better to randomize the list of
questions.

My back-end is SQL Server 2000, but I think the principles apply to Access
as well.

So far I've got a table called OrgQuestions with this structure:

QID, int, key
TopicID, int
QNumber, int
QText, varchar(175)
QOrder, int

The QNumber goes from 1-10 for each of the 10 TopicID's. The QID goes from
1-100, conveniently enough! The QOrder column is what I added to indicate
the (random) order in which the survey questions will be presented to the
survey taker.

In the code I first run a MakeTable query to create a temporary,
single-field table containing just the QID field, renamed to TestQID. 100
rows numbered from 1-100.

I open two RecordSets, rstPik and rstOrd. rstPik is that temporary list of
Question numbers. rstOrd is the OrgQuestions table containing the QOrder
field. I also create a 100 element array to store the random order of the
questions.

Then I run two FOR loops:

For i = 0 To 99
sngSeed = CSng(Time)

' Multiply the random number generated by 100 - i to get a whole number big
enough for
' the list of numbers I need to fill. That 100 - i gets smaller and smaller
as the available
' list of numbers gets smaller.
sngRnd = Rnd(sngSeed) * (100 - i)

' I do the + 1 here to account for the truncation that the INT() function
performs. That is,
' if the Rnd() function gives me .00125, then, even if I multiply it by 100,
the INT() function
' will return 0. The HIGHEST number that the RND() function * 100 returns is
less than 1;
' so I need that + 1 there, too, to ensure that 100 comes back as one of the
"random"
' numbers.
intRnd = Int(sngRnd) + 1
rstPik.MoveFirst

' Move to the row indicated by the random integer - 1 because the MOVE()
function
' moves X rows not counting the current one. I have to account for the
"fencepost"
' error.
rstPik.Move intRnd - 1
intStore = rstPik("TestQID")
intArr(i) = intStore

' Here's where I eliminate the question number that has been used.
rstPik.Delete
rstPik.Update
Next i

' Now the rstPik Recordset is completely empty.
rstOrd.MoveFirst
For i = 0 To 99
rstOrd("QuestionOrder") = intArr(i)
rstOrd.Update
rstOrd.MoveNext
Next i

In a nutshell, fill an array with the randomized sequence of question
numbers and then dump those numbers into the column of the OrgQuestions
table that holds that sequence.

The issue here is, at least with SQL Server, the generation of the random
sequence takes a bit of time. My SQL Server is hosted by my web host and
this routine takes an appreciable amount of time. Does anyone have a
suggestion as to how I might speed this up? With a zippy cable connection,
this process takes over 20 seconds.

--
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com <http://www.swerbach.com>
Security Page: www.swerbach.com/security <http://www.swerbach.com/security>