Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    St. Louis, Missouri, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Record Selection (2000)

    I can't seem to find any kind of sampling or random selection function in Access. I need to run a query that will randomly select 10 records from a specified recordset. I checked with our SQL 'experts' and they didn't seem to know of a function that existed in SQL either. Any ideas on how I can do this?

    Thanks in advance!
    Nickie

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    Nickie,

    I needed to this a few years ago and I did something like this.

    I set up two loops the outer loop was to give me the number of records the inner to select the records.
    I can't remember the random number seed setting but perhaps Hans can help there

    I got my records into a recordset
    set the random number seed
    set the outer loop to go from 0 to 49, (needed fifty random records)
    set the recordset.movefirst
    got a random number
    did inner loop doing recordset.movenext the number of times gotten in random number.
    did what I had to do with the record
    reset recordset.movefirst
    did the next outer loop

    Hope this helps.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Random Record Selection (2000)

    Another method not involving code is to create a random number that is associated with each record, and create a temporary table using a make table query. Then sort the table is ascending random number sequence and at some point in the table simply select 10 records. It's statistically valid and an easy method if staff need to be able to do it repeatedly, and with different numbers of records.

    The Rnd() function is the thing you need to use in your VBA or in a query to produce the random number - you can read about it in the VBA help if you search for random or Rnd.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    I am trying to do the same thing, very unsuccessfully I must say. My question is how would I create a unique random number for each record.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Random Record Selection (2000)

    Do you want to do it in a query or in a table? It can be done in a query by simply putting an expression in the query grid something like:
    <font color=blue><font face="Georgia">RandomNumber: Rnd()</font face=georgia></font color=blue>
    Note that you should execute the Randomize Statement before running this kind of query - see the Access VBA help file for more details. You can execute it in the immediate mode and then it is set for the duration of your Access session. If you want to save the number for some subsequent purpose, then you can either use a make-table query to store pertinent data from each record in addition to the random number. If you want to do it one time, you could use an update query and set a value in a field in the table to the random values. Hope this helps.
    Wendell

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    Here is a rough and ready demo which might make it clearer
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    Is this in Access 97? I can't seem to open it after I unzip it.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Random Record Selection (2000)

    Since the subject is Access 2000, the attached database is probably also Access 2000.
    Wendell

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    Here you have a 97 version.
    Francois

  10. #10
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Record Selection (2000)

    Here is one in Access97 with an extra bit showing sorted records.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

Posting Permissions

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