Random Selection

[trans]

I have a table with 150,000 Records. I am looking to select a random group
of 1,000 records (this number may change) that will show all the fields in
the table. I have seen a few sites on the internet that talk about first
creating a Random Number, which I am able to do. However I get confused when
trying to write the Select Top 10* part of the equation. Is there an easier
way to select these random records or could someone walk me through the
Select equations (ie: Where it goes). Thank you.

 

[John W. Vinson]

I have a table with 150,000 Records. I am looking to select a random group
of 1,000 records (this number may change) that will show all the fields in
the table. I have seen a few sites on the internet that talk about first
creating a Random Number, which I am able to do. However I get confused when
trying to write the Select Top 10* part of the equation. Is there an easier
way to select these random records or could someone walk me through the
Select equations (ie: Where it goes). Thank you.

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. To do so, open the query in design
view and select View... Properties; one of them is "Top Value", just put 1000
in this line.