Results 1 to 3 of 3

Thread: Random Sample?

  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table in MS Access 2003 with customer records for a laptop manufacurer and I need Access to select a sub set of those customers completely randomly.

    The table has 84,475 records and I need a new table with a random 2000 records. It cannot be first 2000 or anything like that - can I pull a random sample?

    Is it a query property?

    I think this can be done and I am lost - thanks again for Woody's!

    Mike D.
    There is always a way.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I have not tested this BUT....

    Assuming you have an Identity (AutoNumber field) in the table

    Then something like this ought to work

    Code:
    Select TOP 2000  IDField, Rnd([IDField]) As RField,Field2, Field3, Field4, etc
    FROM TableName
    ORDER BY Rnd([IDField]) DESC
    Obviously, you can play around with sort order etc
    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    It actually doesn't matter what the arg is to Rnd, as long as it's based on the record. Even if it evaluates to the same value, it forces Rnd to re-evaluate for each record.
    You don't need an ID or AutoNumber field.

    Code:
    Select TOP 2000  IDField, Rnd(Len([PersonsName])) As RField,Field2, Field3, Field4, etc
    FROM TableName
    ORDER BY Rnd(Len([PersonsName])) DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •