Assign Records to differnt users

[JFHBIFF]

Hello everybody,

Thanks for reading my post.

I am wanting to randomly assign different users to records in a split database.
I have a table
UserField
User1
User2
User3
User4

Records Table
RecordField
record1
record2
record3
record4
record5
record6
......could end up being up to 600 records at one time

The records table will have a userID available for assignment.

My thoughts was using an update query. I have had problems trying to figure out how to do it. I can't find a solution in the forum either.

I appreciate any help you could give me.

Thanks,

John

 

[JFHBIFF]

Any ideas?

 

[raskew]

Hi -

The following will update a table, assigning a randomly selected
UserID between 1 and (used 4 here as the example) to each record in
the table.

Because the UserID's are randomly selected, and each selection is
independent of previous selections, you won't end up with
identical numbers of records for each UserID. For example, if you had 800
records in your table, with 4 users numbered 1, 2, 3 or 4, you won't have
200 records for each user. It'll be close, but not exact. And, it'll vary with
each time you call the procedure.

To use, specify/add a field to your table for UserID. Copy the code
to a standard module (ensure the module's name is not the same as the
procedure name). Call it from the debug (immediate) window as shown
below.

Public Sub RndUser(ptbl As String, pfield As String, Top As Integer)
'*******************************************
'Purpose:   Updates each record with a randomly
'           selected UserID between 1 and the
'           maximum (top) number of users as
'           specified by the operator.
'Re:        http://www.access-programmers.co.uk/forums/showthread.php?t=168884
'Coded by:  raskew
'Inputs:    from debug window: call RndUser("Orders3", "UserID", 4)
'Output:    Populates field UserID in each record in table Orders3
'           with a randomly selected number between 1 and 4
'*******************************************

Dim db      As Database
Dim rs      As Recordset
Dim i       As Integer
Dim n       As Integer
Dim x       As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset(ptbl)
rs.MoveLast
rs.MoveFirst
n = rs.RecordCount

Randomize
For i = 1 To n - 1
   rs.Edit
   x = Int((Top * Rnd) + 1)
   rs(pfield) = x
   rs.Update
   rs.MoveNext
Next i
rs.Close
db.Close
Set db = Nothing

End Sub

HTH - Bob

Added:

For example, if you had 800 records in your table, with 4 users
numbered 1, 2, 3 or 4, you won't have 200 records for each user.
It'll be close, but not exact. And, it'll vary with each time you call the
procedure.

If anyone has a logic to work around this issue, without jeopardizing the
random aspect, I'd sure be interested in seeing it.