Automatic Assignment of Workloads

[D.E.N.N.I.S.]

Long time reader, first time poster...I am somewhat of a novice at Access but I believe I have a relatively non-novice problem. I am trying to create an automatic work assignment tool but I am having trouble conceptualizing exactly how to design it.

I have the following queries:

qryOpenWorkloads: generates a list of incompleted workloads
qryActiveQueue: generates a list of users who are available to complete the incomplete workloads

I want to automatically associate the oldest workload from qryOpenWorkloads to the users who have been in the work queue the longest from the qryActiveQueue. Once the users completes the workload, they enter back into the work queue.

My question is, how should I go about this in Access/VBA/SQL?

 

[pbaldy]

Generally speaking, you can get the oldest item in a query with the TOP predicate and the appropriate sorting:

SELECT TOP 1 Field1, Field2
FROM QueryName
ORDER BY FieldName

The overall solution probably requires more knowledge of your tables, etc. Offhand, I'd use recordsets to get the oldest of each and assign the task to the person.

 

[CJ_London]

very similar question asked and answered here

http://www.accessforums.net/showthread.php?t=59742

 

[D.E.N.N.I.S.]

Thanks for the responses...I think I'm on the right track using recordset but I'm stuck on one part. Below is my code. Basically I want to update the [Last Zone] with a value that is from my tblOpenWorkloads.

Private Sub btnCompleteWorkload_Click()
        On Error GoTo ErrorHandler
        sql2 = "SELECT * FROM tblActiveQueue WHERE Stockkeeper = fOSUserName() AND TimeOut Is Null"
        Set rs = CurrentDb.OpenRecordset(sql2)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                If .Updatable Then
                    .Edit
                    ![LastWorkload] = Now()
                    [B]![Last Zone] = 1[/B] 'I want this value to be from tblOpenWorkloads
                    .Update
                End If
            End If
        .Close
        End With
        DoCmd.Requery "qryActiveQueue"
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

My SQL statement to select the correct [Last Zone] would probably be something like this.

sql3 = "SELECT * FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() AND CompleteTime = Select Max(CompleteTime)"

 

[pbaldy]

Or as I suggested:

sql3 = "SELECT TOP 1 * FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime DESC"

 

[D.E.N.N.I.S.]

Right, but where would I input the SQL statement from the other table? I'm trying to populate ![LastZone] with the return value of my sql3 statement.

This is my code now which doesn't return a value.

Private Sub btnCompleteWorkload_Click()
        On Error GoTo ErrorHandler
        sql2 = "SELECT * FROM tblActiveQueue WHERE Stockkeeper = fOSUserName() AND TimeOut Is Null"
        sql3 = "SELECT TOP 1 tblOpenWorkloads.Zone FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime DESC"
        Set rs = CurrentDb.OpenRecordset(sql2)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                If .Updatable Then
                    .Edit
                    ![LastWorkload] = Now()
                    ![LastZone] = CurrentDb.OpenRecordset(sql3)
                    .Update
                End If
            End If
        .Close
        End With
        MsgBox "Complete"
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error"
    Resume ExitSub
End Sub

 

[pbaldy]

You'd open a recordset on the second SQL statement:

Set rs3 = CurrentDb.OpenRecordset(sql3)

and then

![LastZone] = rs3!FieldName

 

[D.E.N.N.I.S.]

That did the trick, thank you sir!

 

[pbaldy]

Happy to help!

 

[D.E.N.N.I.S.]

So I have made a lot of progress regarding the workload assignment tool using a series of recordset updates but I am stuck on one major hurdle. I have a "Complete" button that users press to signify a completed workload and the query runs to automatically assign a new workload. My problem is, if multiple users click "Complete" at the exact same time, they are directed to the same workload.

While instances such as this would be rare, is there anyway to design out the potential of being assigned the same workload if two or more users happen to complete workloads at the exact same time?

If not, is it possible to reduce the amount of time it takes the linked tables on the back-end data table to minimize the impact?