Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return a Random Selection (Access 97)

    I know that this isn't strictly what you're meant to do in a database but hey (!)

    I need to get a random selection returned from a table. I don't care how.

    I was thinking about creating a temporary recordset and then using some kind of random function to return only certain ("randomly" selected) records.
    I'm getting along with this quite well but thought I'd poll peoples opinion on the best (or better) ways of doing this.

    Ideally I'd like to get to a result that returns a user specific number of records (say from 1 - 100) but that these records are returned at random from a base table.

    You thoughts would be appreciated!

    [img]/forums/images/smilies/tongue.gif[/img]

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return a Random Selection (Access 97)

    Dev Ashish has an article on the Access Web here.

    HTH,

    Tom

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Return a Random Selection (Access 97)

    The article from the Access Web gives you one method of doing it. Still another is from within a query. Here's an example of a query based on the Orders table in the Northwind database. It returns 100 pseudo-random (which is about the best you can do) Orders records.

    SELECT TOP 100 Orders.*
    FROM Orders
    ORDER BY Rnd([OrderID]);

    You have to pass Rnd() a value that will differ between records, so an autonumber key is a good choice, but most numeric keys will work if there's a reasonable variation between records.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Return a Random Selection (Access 97)

    Thanks...

    Two very useful methods. I think I'm goignt ot use all three (my module got finished but is only useful in some circumsatces)

Posting Permissions

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