[AccessD] Random numbers lottery-style

Gustav,

Thank you for that analysis. It wasn't so much the randomness that I
was interested in: it was the reduction in the pool of available items
once one of them is "drawn."

The trouble I was having with the collection for Interests I bypassed
by using a function to build a string with all of the Interest numbers
in it and then generate a random number based on the number of items
in the collection and then find that one in the string, remove it from
the string, leaving a shorter string. It worked. I finally was able to
generate over 7000 sample data records that I can use for reporting.

Thanks to everyone who helped out on this. I'm going to have to
remember collections in the future, though I sure haven't figured out
why that one didn't work.

Steve Erbach


On Fri, 04 Mar 2005 12:06:02 +0100, Gustav Brock <Gustav at cactus.dk> wrote:
> Hi Steve
> 
> The last part can be solved by a function and a query.
> You set Picked to True when a record is drawn.
> 
> Calling Randomize outside the query (by an external function) takes
> place in a different scope than that of the query, thus if Rnd(..) is
> run in the query with the same seed initially it will, of course, return
> the same sample or sequence of samples.
> 
> The solution is to generate the random number in the external
> function:
> 
> Public Function RandomNumber( _
>   Optional ByVal booRandomize As Boolean) _
>   As Single
> 
>   Static booRandomized  As Boolean
> 
>   If booRandomize = True Or booRandomized = False Then
>     ' A new seed is requested or this is the first run.
>     Randomize
>     booRandomized = True
>   End If
>   ' Generate and return a random number.
>   RandomNumber = Rnd()
> 
> End Function
> 
> Now the query would look something like this:
> 
>   SELECT TOP n
>     *
>   FROM
>     tblPick
>   WHERE
>     tblPick.Picked = False
>   ORDER BY
>     RandomNumber([ID] Is Null);
> 
> where n is the count of records in the requested sample, and ID is a
> field that is never Null like the primary key.
> The use of ID in the parameter is needed to call RandomNumber not once
> but for every record.
> 
> /gustav