[AccessD] A2003: Random order

<<<
how I might speed this up?
>>>
Steve,

In MS SQL you can use lightning fast query (Northwind db as example):

select CategoryId, CategoryName
  from Categories
  order by  RAND(Checksum(newid()))

This way you get guaranteed random sequence - here is the sequence of
CategoryIds for three runs:

5,7,3,4,2,1,8,6
3,8,1,2,4,5,6,7
8,1,6,2,5,4,7,3

HTH,
Shamil

----- Original Message ----- 
From: "Steve Erbach" <erbachs at gmail.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, October 26, 2005 6:48 PM
Subject: [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>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com