Random Samples

[dejaqhsa]

Using Access2000
I have a database of 16,000 names and addresses. I want to be able to pull
off two groups of names - both groups need to have 2000 names each. I want to
mail these cells with different mailing packs and test which pack worked best.

So both cells need to be exactly the same size and both a random sample of
the whole file.

In a previous bespoke database I was able to use a "1 in N" function - i.e.
to get 2000 names from 16,000 names - the system automatically selected 1 in
every 8 names - therefore giving me a random sample of 2000 names.

But I can't seem to find a funtion in access?
If anyone can help I would be most grateful.

 

[dejaqhsa]

Thanks for the suggestion, most appreicated. Thought it might be the cure,
but although it works for new records added to the database, it just gives
the existing data sequential numbers! Any other suggestions? or am I doing
it wrong?
(in design view - added new field to the database, changed datatype to
autonumber and under general/new values changed to random?)

 

[John Vinson]

In a previous bespoke database I was able to use a "1 in N" function - i.e.
to get 2000 names from 16,000 names - the system automatically selected 1 in
every 8 names - therefore giving me a random sample of 2000 names.

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 if you run the query twice, there's no guarantee that you
won't get repeats (the same customer selected in both runs). You may
want to adapt this to use a constant to generate the same random
number sequence twice; instead of calling Randomize use

Dim vDiscard As Variant
vDiscard = Rnd(3456)

using any arbitrary seed number. Then run the query sorting ascending
for the "top" 2000, then descending for the "bottom".

John W. Vinson[MVP]