Random selection from a table

  • Thread starter Thread starter Steven M. Britton
  • Start date Start date
S

Steven M. Britton

I have a address table that has 4,328 records in it. How
can I make a query or report (or in VBA) that would
randomly select excatly 1,000 of those names and addresses
to send a catalog out too?

Steven M. Britton
 
I have a address table that has 4,328 records in it. How
can I make a query or report (or in VBA) that would
randomly select excatly 1,000 of those names and addresses
to send a catalog out too?
Click to expand...

This won't record who's been sent a catalog, but it will do what you
want:

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.
 
You are assuming (I think) that your random mailouts will eventually cover
everyone equally. But is that actually true? Clearly there is some chance
that in 10 runs (say), certain people might get *no* catalog, and certain
others might get ten! I suggest you get a statistician to calculate some
actual figures.

HTH,
TC
 
Back
Top