Random Samples

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12301-random-samples.html

So both cells need to be exactly the same size and both a random sample of
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
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.

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

dejaqhsa wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301

--
Joseph Meehan

Dia duit

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" wrote:

> dejaqhsa wrote:
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
> > 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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
> > 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.
>
> --
> Joseph Meehan
>
> Dia duit
>
>
>

dejaqhsa wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
> 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" wrote:
>

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

--
Joseph Meehan

Dia duit

On Thu, 29 Jun 2006 03:42:02 -0700, dejaqhsa
wrote:


>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12301
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]