Random Random Random Query or Make Table HELP PLEASE

I have a table that has 25 Team Names in it. I use the well know Select
Random Query process to make a new table (TableTop10) to return only the TOP
10 Team Names each time it is run.

Each Team Name has a total number of employees field [NrEmployees] in the
New TableTop10.

I would like to Select the TeamName and NrEmployees from the New TableTop10
table and then select the Top 10 Employees RANDOMLY between 1 and the field
[NrEmployees] (16 Employees) i.e. 3, 16, 10, 2, 8, 4, 11, 13, 9, 5 frp, each
preset TEAM NUMBER table.

How can I get the Random function to Select the different team names on the
fly as they appear in the results of the first Random TableTop10 Query.



--
Hansford D. Cornett

I don't fully understand what you're trying to do. It sounds as if you
want to select ten numbers at random between 1 and some other number. If
so, here's a VBA function that may help. It returns a string containing
the numbers as a comma-separated list, but that can easily be changed.



Public Function TenRandom(First As Long, Last As Long) As String
Dim N As Long
Dim j As Long, k As Long
Dim ColItems As New Collection
Dim Buf As String

Randomize
On Error GoTo Err_TenRandom
Do Until ColItems.Count = 10
N = Int(Rnd() * (Last - First + 1) + First)
ColItems.Add N, CStr(N)
Loop
On Error GoTo 0
For j = 1 To 10
Buf = Buf & ColItems(j) & ", "
Next
Buf = Left(Buf, Len(Buf) - 2)
TenRandom = Buf
Exit Function

Err_TenRandom:
If Err.Number = 457 Then 'error 457: key already exists
Err.Clear
Resume Next
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation + vbOKOnly
End If
End Function



On Mon, 8 Jan 2007 07:50:01 -0800, Hansford cornett
wrote:

I have a table that has 25 Team Names in it. I use the well know Select
Random Query process to make a new table (TableTop10) to return only the TOP
10 Team Names each time it is run.

Each Team Name has a total number of employees field [NrEmployees] in the
New TableTop10.

I would like to Select the TeamName and NrEmployees from the New TableTop10
table and then select the Top 10 Employees RANDOMLY between 1 and the field
[NrEmployees] (16 Employees) i.e. 3, 16, 10, 2, 8, 4, 11, 13, 9, 5 frp, each
preset TEAM NUMBER table.

How can I get the Random function to Select the different team names on the
fly as they appear in the results of the first Random TableTop10 Query.



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

This is it. This is exactly what I needed thanks for your assistance......


--
Hansford D. Cornett


"John Nurick" wrote:

I don't fully understand what you're trying to do. It sounds as if you
want to select ten numbers at random between 1 and some other number. If
so, here's a VBA function that may help. It returns a string containing
the numbers as a comma-separated list, but that can easily be changed.



Public Function TenRandom(First As Long, Last As Long) As String
Dim N As Long
Dim j As Long, k As Long
Dim ColItems As New Collection
Dim Buf As String

Randomize
On Error GoTo Err_TenRandom
Do Until ColItems.Count = 10
N = Int(Rnd() * (Last - First + 1) + First)
ColItems.Add N, CStr(N)
Loop
On Error GoTo 0
For j = 1 To 10
Buf = Buf & ColItems(j) & ", "
Next
Buf = Left(Buf, Len(Buf) - 2)
TenRandom = Buf
Exit Function

Err_TenRandom:
If Err.Number = 457 Then 'error 457: key already exists
Err.Clear
Resume Next
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation + vbOKOnly
End If
End Function



On Mon, 8 Jan 2007 07:50:01 -0800, Hansford cornett
wrote:

I have a table that has 25 Team Names in it. I use the well know Select
Random Query process to make a new table (TableTop10) to return only the TOP
10 Team Names each time it is run.

Each Team Name has a total number of employees field [NrEmployees] in the
New TableTop10.

I would like to Select the TeamName and NrEmployees from the New TableTop10
table and then select the Top 10 Employees RANDOMLY between 1 and the field
[NrEmployees] (16 Employees) i.e. 3, 16, 10, 2, 8, 4, 11, 13, 9, 5 frp, each
preset TEAM NUMBER table.

How can I get the Random function to Select the different team names on the
fly as they appear in the results of the first Random TableTop10 Query.



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.