Select random records from a table

[TeeCraig]

I'm creating a database for someone that contains a list of employees. Every
6 months they need to randomly select employees to partake in testing. I've
read www.mvps.org/access/queries/qry0011.htm but I'm not quite sure how to
implement and get this to work. Someone to step me through is appreciated.

 

[John Vinson]

I'm creating a database for someone that contains a list of employees. Every
6 months they need to randomly select employees to partake in testing. I've
read www.mvps.org/access/queries/qry0011.htm but I'm not quite sure how to
implement and get this to work. Someone to step me through is appreciated.

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.

Note that this is a truly random selection, in that there is no
checking to see if the employee was selected six months ago. They have
the same chance of being selected again as an employee who's never
been selected.

John W. Vinson[MVP]

 

[TeeCraig]

:

I'm creating a database for someone that contains a list of employees. Every
6 months they need to randomly select employees to partake in testing. I've
read www.mvps.org/access/queries/qry0011.htm but I'm not quite sure how to
implement and get this to work. Someone to step me through is appreciated.

:

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.

Note that this is a truly random selection, in that there is no checking to
see if the employee was selected six months ago. They have the same chance of
being selected again as an employee who's never been selected.

John W. Vinson[MVP]

:

Thanks, John, for the speedy reply. I'm not really new to access, but some
of the features are still strange to me. I think this will be the solution to
my problem, but I still need help. I've created the module as instructed,
where do I add the "Shuffle: RndNum([fieldname])" and then set the "Top
Values" property? I'm using Access 2003.

 

[TeeCraig]

Ok. I got everything set-up, but instead of a random number being generated
per record, it's only generating one random number and applying to all the
records. Each time you run it, it produces a new random number.

I'm creating a database for someone that contains a list of employees. Every
6 months they need to randomly select employees to partake in testing. I've
read www.mvps.org/access/queries/qry0011.htm but I'm not quite sure how to
implement and get this to work. Someone to step me through is appreciated.

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.

Note that this is a truly random selection, in that there is no
checking to see if the employee was selected six months ago. They have
the same chance of being selected again as an employee who's never
been selected.

John W. Vinson[MVP]

 

[Marshall Barton]

It sounds like you didn't use the name of one of your fields
as John said was necessary:
Shuffle: RndNum([fieldname])
--
Marsh
MVP [MS Access]

Ok. I got everything set-up, but instead of a random number being generated
per record, it's only generating one random number and applying to all the
records. Each time you run it, it produces a new random number.

I'm creating a database for someone that contains a list of employees. Every
6 months they need to randomly select employees to partake in testing. I've
read www.mvps.org/access/queries/qry0011.htm but I'm not quite sure how to
implement and get this to work. Someone to step me through is appreciated.

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.

Note that this is a truly random selection, in that there is no
checking to see if the employee was selected six months ago. They have
the same chance of being selected again as an employee who's never
been selected.