Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50

    Question Unanswered: Randomly Select rows

    Hello Everyone,

    Is there any way to randomly select rows from a table in access?

    I need to mark, let's say, 10 items for inspection but I need those 10 items to be randomly selected.

    Any suggestions would be greatly appreciated.

    tjacobs

  2. #2
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Wink Corner Field

    tjacobs,

    I'm glad you posted this message. I have been trying to develop a simular query for many weeks now. So far, no such luck.

    Lemme post what I've found so far...


    SELECT TOP 5 [Name]
    FROM Table1
    WHERE RND(Listing)>0.5;

    Suppose I have the table (Table1) below:
    This query will return 5 random people from the list.

    Hum... I wander why this never worked before for me....
    Attached Thumbnails Attached Thumbnails test.jpg  
    -----

    "How much deeper would the ocean be without sponges?"

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does this work?

    add an integer field SerNum to your table

    run through the recordset incrementing SerNum 1, 2, 3, 4... so each record is sequentially numbered.

    find MaxSerNum
    ...one way is MaxSerNum = DMax("[SerNum]", TableName)

    now find N records from your recordset
    ... where SerNum = int((MaxSerNum * Rnd) +1)

    NOTE that you might get the same record more than once (it's random after all). if you don't like that, you can fix that by keeping the selected records in another recordset and checking each new candidate before adding to the selection.

    izy

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey guys,

    Following Izy's idea, here is a small app to help you along the way. If you need further help or questions just let me know.

    Later, Kal
    Attached Files Attached Files

  5. #5
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Did you guys try it out?????

  6. #6
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Thanks everyone for your suggestions!

    this is the code I used

    DoCmd.RunSQL "INSERT INTO InspectBoxes ( Boxid )SELECT TOP 2 ([boxid]) AS Expr1 FROM Inventory WHERE (((Inventory.Misc) = [Forms]![whichcontainer].[container]) And ((Inventory.Shipped) = False))ORDER BY Rnd([boxid]);"

    DoCmd.RunSQL "UPDATE InspectBoxes INNER JOIN Inventory ON InspectBoxes.Boxid = Inventory.BoxID SET Inventory.Message = 'INSPECT';"

Posting Permissions

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