Assign table values to a larger table

[wbuckles]

Sorry about the subject. Here's a better description:

This is for a basic CRM-type tool. The issues table is imported as a group of records. The "status" column of each record is assigned as Open. There is no "owner" of each issue by default. What I want to do is take a table of users (for example, 10) and assign them to the issues. If there are 100 issues, everyone would get 10 (if there are ten users). Basically, assign them round-robin. When they log in, they get a 'queue' of issues. This part I can do, it the new assignment of issues that has me stumped.

 

[Maquis]

Here's some code that may help you get started. I had to do the same thing in one of my databases. New member records are scanned into a form and when the user clicks a button on the form, each of the form's records are assigned to an Educator. (Stored in the educator table).

This code is a bit more complex than you need because each Educator in my database is assigned a certain percentage of the records. (Kathy gets 30%, Cindy has 10%, Terry gets 50% and Susan gets 10% for example.)

Private Sub Assign_Click()
    Dim dbs As Database
    Dim rs As Recordset, rsEducators As Recordset
    Dim TotRecs As Integer, AssignRecs As Integer, TotEds As Integer
    Dim cntr1, cntr2 As Integer
    Dim RndEds() As String
    Dim temp1 As String, temp2 As String
       
    Set dbs = CurrentDb
    Set rs = RecordsetClone
    Set rsEducators = dbs.OpenRecordset("Educators")
    
    'First assign educators based on percentages
    TotRecs = rs.RecordCount
    TotEds = rsEducators.RecordCount
    ReDim RndEds(TotEds, 2) As String
    rs.MoveFirst
    rsEducators.MoveFirst
    For cntr1 = 1 To TotEds
        AssignRecs = Int(TotRecs * rsEducators!MemPercent / 100)
        For cntr2 = 1 To AssignRecs
            rs.Edit
            rs!Educator = rsEducators!educatorname
            rs.Update
            rs.MoveNext
        Next cntr2
        RndEds(cntr1, 0) = rsEducators!educatorname
        RndEds(cntr1, 1) = CStr(100 * Rnd() + 1)
        rsEducators.MoveNext
    Next cntr1
    Set rsEducators = Nothing
    
    'Okay, now randomly assign the leftovers to educators
    'First sort the RndEd array by the random numbers
    For cntr1 = 1 To TotEds - 1
        For cntr2 = cntr1 To TotEds
            If RndEds(cntr1, 1) > RndEds(cntr2, 1) Then
                temp1 = RndEds(cntr1, 0): temp2 = RndEds(cntr1, 1)
                RndEds(cntr1, 0) = RndEds(cntr2, 0)
                RndEds(cntr1, 1) = RndEds(cntr2, 1)
                RndEds(cntr2, 0) = temp1
                RndEds(cntr2, 1) = temp2
            End If
        Next cntr2
    Next cntr1
    
    'Now assign these educators to members
    cntr1 = 1
    Do Until rs.EOF
        rs.Edit
        rs!Educator = RndEds(cntr1, 0)
        cntr1 = cntr1 + 1
        rs.Update
        rs.MoveNext
    Loop
    Set rs = Nothing
    Set dbs = Nothing
    Me.Refresh

End Sub

Maq B-)
<insert witty signature here>

 

[Maquis]

Oh yeah, I forgot. This code was written for Access 97 using DAO syntax. Access 2000 will need some mods in the variable declarations. Maq B-)
<insert witty signature here>

 

[wbuckles]

Thanks a million! This is exactly what I need, in that in the future, some personnel will get a bigger percentage than others, depending on skill, etc...
This should work perfectly!