How can I randomly select 20 records from my database?

[NETBON]

I am starting to send out quarterly surveys for my internal customers at
work. I need to keep track of who I am sending the survey to and when they
completed it.

I am keeping track of this data in Microsoft Access and I would like to run
a query that would randomly select my next 20 customers to receive the
survey.

I was able to get Rnd function to pull 1 record randomly by using the
following expression: Int((uperbound-lowerbound+1)*Rnd+lowerbound)

However, I am not sure how to get it to select more then 1 without rerunning
the query 20 times.

If anyone can help, that would be wonderful...!!

 

[John Vinson]

I am starting to send out quarterly surveys for my internal customers at
work. I need to keep track of who I am sending the survey to and when they
completed it.

I am keeping track of this data in Microsoft Access and I would like to run
a query that would randomly select my next 20 customers to receive the
survey.

I was able to get Rnd function to pull 1 record randomly by using the
following expression: Int((uperbound-lowerbound+1)*Rnd+lowerbound)

However, I am not sure how to get it to select more then 1 without rerunning
the query 20 times.

If anyone can help, that would be wonderful...!!

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]

 

[David F Cox]

FWIW, tested and working:

SELECT TOP 20 Table1.*, Rnd([ID]) AS sorter
FROM Table1
ORDER BY Rnd([ID]);


ID can be any numeric field within the query which is > 0
It has to have a variable in the RND() function otherwise Access optimises
the query to only call RND() once.

I have used 1.2 * [wanted] to initially select more which are selected down
to 20 by the TOP function. Doing it this way is not statistically correct,
it will give a small bias towards your oldest customers. Ocassionally you
will get less than 20 returned first try.