Randomly pick records for each User

[ren1104]

I have a query with a list of Customer SSNs and Claim Rep, I want to randomly pick 5 Customer SSNs for each of the Claim Rep.
I can't figure out on how to do that, I was able to create an SQL code to just randomly pick the customer SSN.
I don't know anything about Visual Basic codes, but if that is the only solution, please go ahead post it.
Thank you very much in advanced.

 

[Moniker]

Loop through this five times to get five random results.

Randomize
RandomSSN = Int((DCount("*", "YourTableName") - 2) * Rnd + 1)

RandomSSN will return a number between 1 and the number of records in the table that contains your SSNs ("YourTableName"). Use DoCmd.GoToRecord or a .Move method to go to that record and grab the SSN.

 

[ren1104]

Is it possible to do it through SQL? I am really new at Visual basic, not sure where to begin. Here's some more information, would you be able to wirte the codes for me?
Table name: tbl01ClaimInformation: contains the SSN and the Claim Rep Names
Table name: tbl02Result: contains the result of the random pick.

Thank you so much.

 

[The_Doc_Man]

To do this through SQL is possible but very ugly.

Basically you have to add a field to the table, type SINGLE is usually good enough for this - or there is an argument for INTEGER but that requires some math for the scaling.

Write a query that updates this new field, call it ODDSPOT, to a random number by including the RND() function in the query grid for the updated value. Don't change anything else.

OK, now write a query to ORDER BY [ODDSPOT], call it ODDSORT.

For each agent you want to sample, say N records, do a SELECT FIRST {n} .... FROM [ODDSORT] WHERE [AGENT]= {... whatever name you seek...}

Rerun the random update before each selection to change the order.

 

[raskew]

Hi -

Here are two functions you can dump into a module then call per the instructions. It's based on Northwind's Orders table and returns pNum random orderID's and dates attributed to each employee. Should be easily adaptable.

Function Randomizer() As Integer
    'Code courtesy of
    'Joe Foster
    'Source: http://www.mvps.org/access/queries/qry0011.htm
    Static AlreadyDone As Integer
    If AlreadyDone = False Then Randomize: AlreadyDone = True
    Randomizer = 0
End Function

Function RandomOrders(pMyTable As String, pNum As Integer) As String
'*******************************************
'Purpose:   Returns pNum randomly selected
'           orders from Northwind's Orders
'           table for each distinct employee.
'Coded by:  raskew
'Calls:     Function Randomizer, written by
'           Joe Foster and displayed in the Access Web at
'           http://www.mvps.org/access/queries/qry0011.htm
'Inputs:    from debug window: ? RandomOrders("zTestRan", 5)
'Output:    table zTestRan
'*******************************************

Dim db As Database
Dim rs As Recordset
Dim tName As String, strSQL As String
Dim n As Integer, i As Integer, empHold As Integer
 
    Set db = CurrentDb
    tName = pMyTable
    On Error Resume Next
    
    'Step (1) Does table tName exist?  If so, delete it.
    db.Execute "DROP TABLE " & tName & ";"
    Err = 0
    '*******************
    
    ' Step (2) ' Create new table.
    strSQL = "CREATE TABLE " & tName & " (EmployeeID Long," _
    & " OrderID Long, OrderDate Date" & ");"
    db.Execute strSQL
    '*******************
    
    ' Step (3) ' Create recordset of employees
    strSQL = "SELECT distinct EmployeeID FROM Orders;"
    Set rs = db.OpenRecordset(strSQL)
    ' count the employees
    rs.MoveLast
    n = rs.RecordCount
    rs.MoveFirst
    '*******************
    
    ' Step (4) Create random listing of orders for each employee
    For i = 1 To n  'loop thru the listing of employees
        empHold = rs!EmployeeID
      ' create append query
        strSQL = "INSERT INTO " & tName & " ( OrderID, EmployeeID, OrderDate )" _
            & " SELECT TOP " & pNum & " Orders.OrderID, Orders.EmployeeID, Orders.OrderDate" _
            & " FROM Orders" _
            & " WHERE (((Orders.EmployeeID)= " & empHold & " ) AND ((randomizer())=0))" _
            & " ORDER BY Rnd(IsNull([Orders].[OrderID])*0+1);"
    ' execute the query
        db.Execute strSQL
        rs.MoveNext
    Next i
    '*******************
    
    docmd.OpenTable tName, acViewNormal
    RandomOrders = "Open table " & tName & " to view the results."
    
    ' Cleanup
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End Function

HTH - Bob