[AccessD] A2003: Random order

Steve,

I should have written that this query is enough:

select CategoryId, CategoryName
   from Categories
  order by  newid()

to get random order of source records.

But RAND(Checksum(newid())) make generated values a kind of normalized.
Additionally you can write:

Convert(int, Floor(Rand(Checksum(newid()))*100+1))

to get integer random IDs in the range 1-100. But take into account that
they can be non-unique.

For pure mdb +VBA solution you can use similar approach with a function like
the one described here

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q176790

to get random seed string, which can be converted to random ID similarily to
MS SQL solution.

Shamil

----- Original Message ----- 
From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, October 26, 2005 9:37 PM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com