Results 1 to 3 of 3
  1. #1
    Madmax is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    80

    Random Record pull


    Goodmorning,

    I have a bit of a hard one (For a newer person lol ). New database being setup with basically a test bank. The idea is one table with [personnal info], one table with 100 [Q&A Bank](lets say [Q] Field and [A] Field). lastly, one table to track [results]. I need a way to pull 20 random records each time a new record is added to the [Results] Table on a form. The idea is to use the current user function as a tracker and have this group of people log in, start a new test(open the form to a new record on [Results Table]. Then, Pull 20 Random Records from the [Q&A Bank] Table both [Q] and [A] Fields and then calculate and enter the info into the . Is this possible or am I dreaming?

    Max

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you can randomize any given table by throwing the data into an array and randomizing THAT. It's certainly not novice-type coding, so I would start with google here:

    http://www.google.com/#sclient=psy&h...1&pf=p&pdl=300

    I think people scoff at the method that those articles are using, but I can't remember why. I don't think rnd() by itself can always be trusted. It's been a while since I've researched seeding.

    so you'd probably want:

    Code:
    select AutonumberField, field2, field3 .......
    
    ORDER BY rnd([autoNumberField])

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Here's a sample of code I use to create a random number (starting with 80000).

    Function CreateRandomNumbersForMegan()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    Dim RNum As Variant
    Dim RanNum As Integer
    Dim X As Integer
    strSQL = "Select * from Sheet1"
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    For X = 1 To 802
    GetNewNum:
    Randomize
    RanNum = Int((10000 * Rnd) + 1)
    RNum = 80000 + RanNum 'Random number starts at 80000.
    If IsRandomNumTaken(RNum) = True Then
    GoTo GetNewNum
    Else
    rs!NewRandomSID = RNum
    rs.Update
    rs.MoveNext
    End If
    Next X
    rs.Close
    Set rs = Nothing
    MsgBox "Done!!"
    End Function
    Function IsRandomNumTaken(RNum As Variant) As Boolean
    Dim rx As ADODB.Recordset
    Set rx = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from Sheet1 where NewRandomSID = " & RNum & ""
    rx.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rx.EOF And rx.BOF Then
    IsRandomNumTaken = False
    Else
    IsRandomNumTaken = True
    End If
    rx.Close
    Set rx = Nothing
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Random 0 now
    By Mounds in forum Access
    Replies: 4
    Last Post: 04-29-2011, 10:04 AM
  2. cannot pull the correct record
    By simba in forum Reports
    Replies: 1
    Last Post: 11-30-2010, 10:45 AM
  3. Code to pull in data from a specific record
    By jdunn36 in forum Access
    Replies: 1
    Last Post: 09-20-2010, 11:54 AM
  4. Random sampling
    By uscpa in forum Queries
    Replies: 4
    Last Post: 01-23-2010, 03:12 AM
  5. random record
    By wasim_sono in forum Programming
    Replies: 1
    Last Post: 02-06-2008, 02:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums