How do I create a random sample from a table?

[Traci]

I'm new to Access and I'm need to take a random sample from a table. I have
bwtween 900 - 1400 accounts numbers a day and I need to pull a random sample
of 150 each day. Can anyone help me on how to do this? (I'm running off
Access 2002). Thank you!

 

[John Vinson]

I'm new to Access and I'm need to take a random sample from a table. I have
bwtween 900 - 1400 accounts numbers a day and I need to pull a random sample
of 150 each day. Can anyone help me on how to do this? (I'm running off
Access 2002). 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.

John W. Vinson[MVP]

 

[Douglas J. Steele]

I'm new to Access and I'm need to take a random sample from a table. I
have
bwtween 900 - 1400 accounts numbers a day and I need to pull a random
sample
of 150 each day. Can anyone help me on how to do this? (I'm running off
Access 2002). 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:

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.

Just a little warning to John's elegant solution.

Remember that random numbers CAN repeat. The Top Values property of a query
does not handle ties: if when you've sorted the query, the 150th and 151st
elements both have the same value for Shuffle, you'll actually get 151 rows
returned.

Of course, the probability of this happening is low.