Query to pull random selection of records by manager name

[jbeets]

Hello!

The database I am creating is used to audit work completed by managers. I am trying to find the most efficient way to pull a random selection of records for each manager under a director.

I have a query that pulls in all of the records completed by the managers for that area and I have a parameter set for the director to type in their name so that it pulls in audits completed by only their managers. Is there a way to pull in a set number of randomly selected audits for each manager? Is it possible to tell it to pull only unique values from the manager column?

This is what I have so far....

SELECT tblAutoAudits.AuditName, tblAutoAudits.Unit, tblAutoAudits.Manager, tblAutoAudits.UserName, tblAutoAudits.[Claim Number], tblAutoAudits.LossDate, tblAutoAudits.[Date Audit Pulled], tblAutoAudits.[Loss Description], tblAutoAudits.COL, tblAutoAudits.Auditor, tblAutoAudits.DateAudited, tblAutoAudits.Coverage, tblAutoAudits.[Investigation/ Liability], tblAutoAudits.Financials, tblAutoAudits.APD, tblAutoAudits.[Injury Evaluation & Settlement], tblAutoAudits.[Documentation/ File Coding], tblAutoAudits.[Communication & Customer Service], tblAutoAudits.[Audit Comments], tblAutoAudits.[Positive Feedback], tblAutoAudits.[Opportunity for Improvement], tblAutoAudits.[Overall Score], tblAutoAudits.[Skip Reason], tblAutoAudits.Area, tbl_Directors.[Director Name], Rnd([Overall Score]) AS Random
FROM tblAutoAudits INNER JOIN tbl_Directors ON tblAutoAudits.Area = tbl_Directors.Area
WHERE (((tblAutoAudits.[Overall Score]) Is Not Null) AND ((tbl_Directors.[Director Name])=[Enter Director name for audits to complete - (example: Smith, John)]));

Thank you for any ideas or help you can provide!

[CJ_London]

look to the bottom of this thread in the Similar threads section - there are several posts asking the same thing

[ranman256]

This code goes thru a list of manager, pulls the 1 mgr,
gets his data, then picks N random records (kiRandCt )

   
Public Sub RandomOrder()
dim coll as new collection
cont kiRandCt = 4   'pull 4 random records

docmd.setwarnings false
'empty the result table
docmd.openquery "qdEmptyTargTbl"

  'go thru list of managers and get N random entries they own
for i = 0 to me.lstMgr.listcount-1
   vMgr =  lstMgr.listitem(i)   'get next item in list
   lstMgr = vMgr    'set list to this item
   
       'get N random records for this mgr
    gosub getRandomRecs 
next
docmd.setwarnings true
MsgBox "done"
Set coll = Nothing
exit sub

getRandomRecs :
set coll = new collection
   'get mgr records
sSql = "select * from table where [Manager]='" & vMgr & "'"
  'collect all keys in the rec set
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
   While Not .EOF
       sKey = rst.Fields(0).Value & ""   'get the key
       coll.Add sKey, sKey      'put key in list, then remove if picked       
       .MoveNext
   Wend
End With
Set rst = Nothing
   'pick key at random then set new order for it
i = 0
While coll.Count > 0 and iPickCt < kiRandCt 
    iPickCt  = iPickCt + 1
    iRnd = Int(coll.Count * Rnd + 1)
    'Debug.Print iRnd
    
    vKey = coll(iRnd)
        'place random pick into target table
     sSql = "insert into tTargTbl (manager, key) values ('" & vMgr & "'," & vKey & ")"
    DoCmd.RunSQL sSql
    coll.Remove sKey    'remove key so we dont duplicate
Wend
return
End Sub

[jbeets]

Thank you ranman256! I will give this a shot and see how it goes!

But first lets see if I understand.....I will put this code in a module in my form and adjust the query it is opening along with the number of records I want it to pull for each manager. The form will then populate with the records pulled?

Again, thank you so much for taking the time to help me!