Random nthing in access

[Guest]

Hi,

I have 308580 records in a particular table. I would like to randomly select
20,000 of them. Is there any way to do this in Access 2003? Help!!!

 

[Michel Walsh]

SELECT TOP 20000 *
FROM tableName
ORDER BY RND(numericalFieldName)


should do. You may have to RANDOMIZE (see help file) to generate a different
sequences.


Vanderghast, Access MVP

 

[John W. Vinson]

Hi,

I have 308580 records in a particular table. I would like to randomly select
20,000 of them. Is there any way to do this in Access 2003? Help!!!

curious... third time today I've replied to a similar request; haven't needed
to in weeks before!

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]