Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Random Samples

Discussion in 'Information Technology' started by dejaqhsa, Jul 28, 2006.

Tags:
  1. dejaqhsa

    dejaqhsa
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Is miracle less about randomness and more about faith ? Intellectual Articles Oct 14, 2015
    Christianity Pope: Humanity isn't random product of evolution Interfaith Dialogues Apr 24, 2011
    Random Process in Sikhism Sikh Sikhi Sikhism Aug 20, 2008
    Random of Act of kindness... Inspirational Stories Mar 25, 2008
    Conspriacy theory ! Random thoughts Sikh Youth Oct 2, 2006

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

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

    --
    Joseph Meehan

    Dia duit
     
  4. dejaqhsa

    dejaqhsa
    Expand Collapse
    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" 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
    > > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    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
    > 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
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 29 Jun 2006 03:42:02 -0700, dejaqhsa
    <dejaqhsa@discussions.microsoft.com> 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
    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]
     

Share This Page