Random Query

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I have 50,000 names in a table and I need to randomly pick
10,000. How or what is the best way to set a query so I
can accomplish this task. Thanks!
 
I have 50,000 names in a table and I need to randomly pick
10,000. How or what is the best way to set a query so I
can accomplish this task. Thanks!
Click to expand...


You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double Static
bRnd As Boolean If Not bRnd Then 'Initialize the random
number generator once only bRnd = True Randomize End If
RndNum = Rnd() End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.
 
John -

I think your code lines ran all together........!

--
Ken Snell
<MS ACCESS MVP>

John Vinson said:
I have 50,000 names in a table and I need to randomly pick
10,000. How or what is the best way to set a query so I
can accomplish this task. Thanks!
Click to expand...


You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double Static
bRnd As Boolean If Not bRnd Then 'Initialize the random
number generator once only bRnd = True Randomize End If
RndNum = Rnd() End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.
Click to expand...
 
I have 50,000 names in a table and I need to randomly pick
10,000. How or what is the best way to set a query so I
can accomplish this task. Thanks!
Click to expand...

OOPS!

My Agent let me down. Thanks Ken... The code should be:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function
 
Back
Top