Random Samples

[Guest]

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.

 

[Joseph Meehan]

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.

One way would be to use an autonumber field and use the random version
of auto number, then you would only need to sort on that field and pick off
the first 2,000 names. The second group could be the next 2,000 or the last
2,000 if you like or if you want to include the possibility of someone being
selected for both samples, just do an additional random autonumber.

 

[Guest]

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?)

 

[Joseph Meehan]

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?)

:

You could try making a new table with the additional field and then use
an update query to populate it. (Making an new table by copying just the
structure of the existing table would make it easier.)

 

[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]