Select n random records per group (1 Viewer)

SELECT  DISTINCT
    t.EmployeeID
  , Orders.Freight
FROM
   Orders AS t 
LEFT JOIN
   Orders 
ON
   t.EmployeeID = Orders.EmployeeID
WHERE
   (((Orders.Freight) In (
SELECT
    Top 2 [Freight] 
FROM
    Orders 
WHERE
    [EmployeeID]= t.[EmployeeID] 
ORDER BY
    [Freight] DESC)));

Public Sub RandomOrders(pMyTable As String, pNum As Integer)
'*******************************************
'Name:      RandomOrders (Function)
'Purpose:   Returns pNum randomly selected
'           orders from Northwind's Orders
'           table for each distinct employee.
'Re:        http://www.utteraccess.com/forums/showflat.php?Cat=&Board=access_97&Number=242714&page=0&view=collapsed&sb=5&o=&fpart=1#Post243004
'Code 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: call RandomOrders("zTestRan", 3)
'Output:    table zTestRan
'*******************************************

Dim db As Database
Dim rs As Recordset
Dim tName As String, test As String, strSql As String
Dim n As Integer, i As Integer, empHold As Integer, j As Integer
 
Set db = CurrentDb
tName = pMyTable
On Error Resume Next

'Step (1) Does table tName exist?  If so, delete it.
test = db.TableDefs(tName).name
If Err <> 3265 Then
   docmd.SetWarnings False 'Hide warning messages
   docmd.DeleteObject acTable, tName
   docmd.SetWarnings True  'Reinstate warning messages
End If
Err = 0
'*******************

' Step (2) ' Create new table.
strSql = "CREATE TABLE " & tName & " (EmployeeID Long," _
& " OrderID Long, Freight Currency" & ");"
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, Freight )" _
        & " SELECT TOP " & pNum & " Orders.OrderID, Orders.EmployeeID, Orders.Freight" _
        & " 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
j
'*******************

docmd.OpenTable tName, acViewNormal

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

Function Randomizer() As Integer
'Source: http://www.mvps.org/access/queries/qry0011.htm
'Author: Joe Foster

Static AlreadyDone As Integer
   If AlreadyDone = False Then Randomize: AlreadyDone = True
   Randomizer = 0
End Function

SELECT table.field1, table.field2 FROM table
   GROUP BY table.field1
      ORDER BY rnd(table.field2)

SELECT  DISTINCT
    t.EmployeeID
  , Orders.Freight
FROM
   Orders AS t 
LEFT JOIN
   Orders 
ON
   t.EmployeeID = Orders.EmployeeID
WHERE
   (((Orders.Freight) In (
SELECT
    Top 2 [Freight] 
FROM
    Orders 
WHERE
    [EmployeeID]= t.[EmployeeID] 
ORDER BY
    [color=red]rnd([Freight])[/color] DESC))));

   strSql = "INSERT INTO " & tName & " ( OrderID, EmployeeID, Freight )" _
        & " SELECT TOP " & pNum & " Orders.OrderID, Orders.EmployeeID, Orders.Freight" _
        & " FROM Orders" _
        & " WHERE (((Orders.EmployeeID)= " & empHold & " ) AND ((randomizer())=0))" _
        & " ORDER BY Rnd(IsNull([Orders].[OrderID])*0+1);"

Private Sub AppendTemp(Emp As Long, TopVal As Long)

Dim StrSql As String

'StrSql1 = "INSERT INTO TempRandomTopPerGroup ( EmployeeID, Freight ) SELECT TOP " & TopVal
'StrSql1 = StrSql1 & " Orders.EmployeeID , Orders.Freight FROM Orders "
'StrSql1 = StrSql1 & "WHERE Orders.EmployeeID = " & Emp
'StrSql1 = StrSql1 & " ORDER BY Orders.Freight DESC;"

    StrSql = "INSERT INTO TempRandomTopPerGroup ( EmployeeID, Freight )" _
        & " SELECT TOP " & TopVal & " EmployeeID, Freight" _
        & " FROM Orders" _
        & " WHERE Orders.EmployeeID = " & Emp & " AND randomizer()=0 " _
        & " ORDER BY Rnd(IsNull([Orders].[OrderID])*0+1);"

CurrentDb.Execute StrSql

End Sub

SELECT  DISTINCT
    t.EmployeeID
  , Orders.Freight
FROM
   Orders AS t 
LEFT JOIN
   Orders 
ON
   t.EmployeeID = Orders.EmployeeID
WHERE
   (((Orders.Freight) In (
SELECT
    Top 2 [Freight] 
FROM
    Orders 
WHERE
    [EmployeeID]= t.[EmployeeID] 
ORDER BY
    [Freight] DESC)));

SELECT [LastName] & ", " & [FirstName] AS Name, 
Orders.Freight
FROM Employees 
LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.Freight) 
In (SELECT Top 2 [Freight] FROM Orders 
WHERE [EmployeeID]= Employees.[EmployeeID] 
ORDER BY 
rnd*([OrderId]) desc)));

Option Compare Database
Dim RandNum As Long

Function GetRandom()

If RandNum > 100 Then

        RandNum = 0

    Else

        RandNum = RandNum + (Int((100 - 1 + 1) * Rnd + 1))

    End If

    GetRandom = RandNum

End Function

SELECT [LastName] & ", " & [FirstName] AS Name, 
Orders.Freight
FROM Employees 
LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.Freight) In (SELECT Top 2 [Freight] FROM Orders 
WHERE [EmployeeID]= Employees.[EmployeeID] 
ORDER BY 
Getrandom()*([OrderId]) desc)));