Randomly select a given number of records

  • Thread starter Thread starter Richard Hope
  • Start date Start date
R

Richard Hope

Hi there -

Is it possible to a select a defined number of records
from a table, but a random spread? What I would like to do
is (for example) to select 3100 records randomly from a
table of 18,734. I know how to select one in three, one in
four etc, but I then have to manually delete back to the
required amount which removes the random element and
doesn't result in a representative spread of the table.

Thanks,

Richard.
 
Hi there -

Is it possible to a select a defined number of records
from a table, but a random spread? What I would like to do
is (for example) to select 3100 records randomly from a
table of 18,734. I know how to select one in three, one in
four etc, but I then have to manually delete back to the
required amount which removes the random element and
doesn't result in a representative spread of the table.
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.
 
Back
Top