[AccessD] Random ina query

gustav,

  Well I just tried it here and it works fine.  On a table of 16,000
records, I asked for the top 5 and I got a different set of five records no
matter how many times I executed the query.

Jim.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 15, 2009 12:15 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Random ina query

Hi Jim

No. As I wrote to Susan (note the word "initially"):

<quote>
I had to deal with this and found out that the common method spread around
the web doesn't work.

The problem is that calling Randomize outside the query (by an external
function) takes place in a different scope than that of the query, thus
Rnd(..) is run in the query with the same seed initially which, of course,
will return the same sample or sequence of samples.
</quote>

The years passed by and Susan forgot all about it, so in 2007 she wrote a
tip without mentioning this trap:

  http://blogs.techrepublic.com.com/howdoi/?p=149

She does discuss a UDF but, unfortunately, that is for another purpose.

/gustav


>>> jimdettman at verizon.net 15-05-2009 17:42:19 >>>

  I forgot to add a field to the call, but otherwise, that will work. With
it defined as I posted, it would get called once at the start of the query.
It needs to be Rnd([<fieldname>]).   If you don't have a non-zero numeric
field, you can do Rnd(Len([<field name>])

  But other then that, it does work.

Jim.

  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 15, 2009 11:25 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Random ina query

Hi Jim

Not you! I've posted that code many times here and 14 times at [you know
where] since 2004.

I even found my function here (with left out credits of course):

  http://www.vbforums.com/showthread.php?t=388276 

/gustav


>>> jimdettman at verizon.net 15-05-2009 17:07:48 >>>

  Yup.  Define a column as Rnd(1), sort on that column and set the TOP
predicate for the query for the number of records you want.



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com