Assign Random number to a Record

[Rod]

Hello,

I have a table, tblNBAC of over 100,000 name. I am looking to run a query
to do:
1) Count the total records in the table.
2) Divide the total record count by 20 for twenty people.
3) Update [Owner] to be a randomized number >0 and <21 which will represent
the 20 people; it is critical they each need to get the same number of
records. If there are some records left over after the division then leave
those records unassigned. The default value for an unassigned record is
[Owner]=0

Once the people all have assigned records a report filtering based on the
owner can be created and distributed to the users.

 

[Stefan Hoffmann]

hi Rod,

1) Count the total records in the table.
2) Divide the total record count by 20 for twenty people.

Why that?

3) Update [Owner] to be a randomized number >0 and <21 which will represent
the 20 people; it is critical they each need to get the same number of
records. If there are some records left over after the division then leave
those records unassigned. The default value for an unassigned record is
[Owner]=0

Is

UPDATE [Owner] = Int(Rnd * 20 + 1)

not sufficent?

In general

=Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)

mfG
--> stefan <--

 

[Rod]

Hi Stefan,

It is critical that everyone gets the same number of records. How can I
insure I do not have someone coming back to me with a complaint of this
nature?

hi Rod,

1) Count the total records in the table.
2) Divide the total record count by 20 for twenty people.

Why that?

3) Update [Owner] to be a randomized number >0 and <21 which will represent
the 20 people; it is critical they each need to get the same number of
records. If there are some records left over after the division then leave
those records unassigned. The default value for an unassigned record is
[Owner]=0

Is

UPDATE [Owner] = Int(Rnd * 20 + 1)

not sufficent?

In general

=Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)

mfG
--> stefan <--

 

[Rod]

Stefan,

Where does UPDATE [Owner] = Int(Rnd * 20 + 1) go in the query?

hi Rod,

1) Count the total records in the table.
2) Divide the total record count by 20 for twenty people.

Why that?

3) Update [Owner] to be a randomized number >0 and <21 which will represent
the 20 people; it is critical they each need to get the same number of
records. If there are some records left over after the division then leave
those records unassigned. The default value for an unassigned record is
[Owner]=0

Is

UPDATE [Owner] = Int(Rnd * 20 + 1)

not sufficent?

In general

=Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Rod,

It is critical that everyone gets the same number of records. How can I
insure I do not have someone coming back to me with a complaint of this
nature?

Ok, then you need to do in VBA, something like:

RecordCount = DCount("*", "Table")
Set rs = OpenRecordset("Table")

For OuterCount = 1 to RecordCount Step 20
ClearArray
For InnerCount = 1 To 20
Do
RandomNumber = Int(20 * Rnd + 1)
If NotInArray(RandomNumber) Then
rs![Owner] = RandomNumber
PutInArray RandomNumber
Exit Do
End If
Loop
Next InnerCount, OuterCount

You need to implement the helper functions ClearArray, NotInArray and
PutInArray your self.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Rod,

Where does UPDATE [Owner] = Int(Rnd * 20 + 1) go in the query?

It is a simple update query:

UPDATE [YourTableName] SET [Owner] = Int(Rnd * 20 + 1)


mfG
--> stefan <--

 

[Rod]

Right, figured that one out, but it puts the same number in [Owner] for all
of the records. I'll look at your previous post...

hi Rod,

Where does UPDATE [Owner] = Int(Rnd * 20 + 1) go in the query?

It is a simple update query:

UPDATE [YourTableName] SET [Owner] = Int(Rnd * 20 + 1)


mfG
--> stefan <--

 

[Rod]

OK, looks good. Can you help me out on the declarations? Would it look like
Dim ClearArray[?] number 'I would expect a 20x5000 array to be sufficient.

hi Rod,

It is critical that everyone gets the same number of records. How can I
insure I do not have someone coming back to me with a complaint of this
nature?

Ok, then you need to do in VBA, something like:

RecordCount = DCount("*", "Table")
Set rs = OpenRecordset("Table")

For OuterCount = 1 to RecordCount Step 20
ClearArray
For InnerCount = 1 To 20
Do
RandomNumber = Int(20 * Rnd + 1)
If NotInArray(RandomNumber) Then
rs![Owner] = RandomNumber
PutInArray RandomNumber
Exit Do
End If
Loop
Next InnerCount, OuterCount

You need to implement the helper functions ClearArray, NotInArray and
PutInArray your self.


mfG
--> stefan <--

 

[Rod]

Stefan, would you please comment the lines of the code so I can follow your
line of thinking and try to finish the coding?
Thanks.

hi Rod,

It is critical that everyone gets the same number of records. How can I
insure I do not have someone coming back to me with a complaint of this
nature?

Ok, then you need to do in VBA, something like:

RecordCount = DCount("*", "Table")
Set rs = OpenRecordset("Table")

For OuterCount = 1 to RecordCount Step 20
ClearArray
For InnerCount = 1 To 20
Do
RandomNumber = Int(20 * Rnd + 1)
If NotInArray(RandomNumber) Then
rs![Owner] = RandomNumber
PutInArray RandomNumber
Exit Do
End If
Loop
Next InnerCount, OuterCount

You need to implement the helper functions ClearArray, NotInArray and
PutInArray your self.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Rod,

Right, figured that one out, but it puts the same number in [Owner] for all
of the records. I'll look at your previous post...

Try

UPDATE [YourTableName] SET [Owner] = Int(Rnd([ID]) * 20 + 1)

where ID is an autocrement in your table. Without using it, Jet thinks
that Rnd is deterministic and reuses the first value.



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Rod,

OK, looks good. Can you help me out on the declarations? Would it look like
Dim ClearArray[?] number 'I would expect a 20x5000 array to be sufficient.

That's too much. The array is used to store the used random numbers
(1-20) per run in the inner loop:

So it may be something like this:

Private m_Array[1 To 20] As Long

Private Sub ClearArray()

Dim Count As Long

For Count = 1 To 20
m_Array[Count] = -1
Next Count

End Sub

Private Sub PutInArray(ANumber As Long)

Dim Count As Long

For Count = 1 to 20
If m_Array(Count) = -1 Then
m_Array(Count) = ANumber
Exit For
End If
Next Count

End Sub

Private Function NotInArray(ANumber As Long) As Boolean

Dim Count As Long
Dim Result As Boolean

Result = False

For Count = 1 to 20
If m_Array(Count) = ANumber Then
Result = True
Exit For
End If
Next Count

NotInArray = Result

End Function

mfG
--> stefan <--