Assistance needed in finding the correct pickup time

[Voyager]

Hi Experts,
I despirately need your assistance in solving one of my problem. I wanted to communicate the correct pickup time to those who delegate tasks to my team.
I have attached a dummy db for your reference.
Scenario : I have 3 team members and 15 tasks. First member gets free by 1AM and second by 2AM and thrid by 3AM in the pickup time field in my db I managed to get 1 AM as the pickup time but that is not right since I have already 13 more tasks in hand and first person who gets free by 1 am will take the task of 4 AM (Task4) and like wise second person will pick Task5
So the correct pickup time should be 13:00 Not 1:00.
Is there anyway you can help me out in solving this problem.

 

[mike60smart]

Hi

Your table structure is not correct

Each Employee would normally have a set Number of Tasks and should be linked using a Main Form / Subform

In your case If you want to allocate Pending Tasks from a Task List then you should have a List box containing the lists of Pending Tasks and a Combobox to select an Employee.

Your process should be :-

1. select a Task from the List
2. then when you select an Employee using the Combobox you can allocate it to an Employee using VBA

 

[arnelgp]

how did you arrived at 13 as pickup time?
i did some testing.
add this function in Dashboard form:

Public Function fnPickUpTime() As Variant
    Dim rs As DAO.Recordset
    fnpicuptime = ""
    With Me.[Queform].Form
        Set rs = .RecordsetClone
        Set rs = rs.OpenRecordset
        With rs
            If Not (.BOF And .EOF) Then .MoveFirst
            Do While Not (.EOF)
                If !Status = "Pending" Then
                    fnPickUpTime = !tasktime
                    Exit Do
                End If
                .MoveNext
            Loop
        End With
        Set rs = Nothing
    End With
    If (fnPickUpTime & "" = "") Then fnPickUpTime = #1:00:00 AM#
End Function

add another unbound textbox on Dashboard form, for a test.
put this as its Control Source:
[/code]
=fnPickUpTime()[/code]

 

[Voyager]

Hi arnelgp Sir,
Thank you as always your answer was to the point. Your code gave a time of 04:00 AM which is correct. Since after the completion of Task1 he will pick Task4 and be free at 04:00 AM.

I needed one more step after achieving this 4:00 . Which means after completion of Task4, he will pick Task7,Then Task10 and finally Task13 hence and his free time will be 13:00 corresponding to task13

Is there a way in vba to get this 13:00 answer

 

[isladogs]

If I've understood your requirements correctly, then this update query will do what you want

UPDATE tasks SET tasks.Employee = "Emp" & IIf(Mid([Taskid],5) Mod 3<>0,Mid([taskid],5) Mod 3,3);

However I agree with the earlier comment that your table structure isn't correct

 

[Voyager]

Hi all,
Thanks for your immediate assistance all of your answers are too close but due to its complexity I could not move further. Let us not focus more on the table part since I have just created a dummy db file of slightly complex and bigger database and in the original database employee names will be assigned automatically via an outlook macro. For now lets us assume Employee field is manually updated.

Hello isladogs,
Thanks you. Your answer is very close but If I add one more employee ( say emp4) it ignores the new employee. i.e. the code takes only the given three employees.
apart from that the names will be different in the real life i.e. emp1,e emp2 etc will be replaced with their actual names so instead of using mod function is there any workaround for that.

Apart from that I have added a availability. Our code should look for no. of employees and then for pending tasks if any then it should check if pending task time is greater than available time if it is so then it should look for next emp. Is this possible.?

 

[isladogs]

My solution could be modified but it was only intended to work for that one rather artificial example of yours.
The code could be adapted for emp4 but the structure will still be wrong.
At some point you'll have an employee 5 and need to review the allocation again.
If this is a real world scenario then you need an approach that will work in the real world.
Good luck with your project

 

[Voyager]

Hi isladogs,
Though your code was helpful, I will try to customise to meet my needs. I a, sorry if I didn’t get it right in the dummy db.
However thanks for the help.

 

[arnelgp]

on your post #4, you mean you need to auto populate the task with each employee?

 

[Voyager]

Yes sir, is there a way to auto assign tasks for an employee. And I want the time of the last task. So that it will be the When he can pick another job. Basically queue time.

 

[arnelgp]

add another field "taskdate" (date/time, format: short date) to Tasks table.
add this code on the Open Event of the Dashboard form:

Private Sub Form_Open(Cancel As Integer)
    Dim rsTask As DAO.Recordset
    Dim rsTask2 As DAO.Recordset
    Dim rsEmp As DAO.Recordset
    Dim bolsecondpass As Boolean
    
    Set rsTask = CurrentDb.OpenRecordset("select * from tasks where taskdate=#" & Format(Date, "mm/dd/yyyy") & "#")
    With rsTask
        If (.BOF And .EOF) Then
            Set rsTask2 = CurrentDb.OpenRecordset("select task2.* from task2 order by id", dbOpenSnapshot)
            Set rsEmp = CurrentDb.OpenRecordset("select emp.* from emp order by id", dbOpenSnapshot)
            rsTask2.MoveFirst
            rsEmp.MoveFirst
            Do While Not rsTask2.EOF
                .AddNew
                !taskid = rsTask2!taskid
                !taskdate = Date
                !tasktime = rsTask2!tasktime
                If bolsecondpass Then
                    !status = "Pending"
                Else
                    !status = "In Progress"
                End If
                !employee = rsEmp!ename
                .Update
                rsTask2.MoveNext
                rsEmp.MoveNext
                If rsEmp.EOF Then
                    rsEmp.MoveFirst
                    bolsecondpass = True
                End If
            Loop
        End If
    End With
    Set Me.Queform.Form.Recordset = rsTask
    Set rsTask = Nothing
    Set rsTask2 = Nothing
    Set rsEmp = Nothing
                    
    Me.ests_subform.Form.RecordSource = "select employee as ename,tasktime from tasks " & _
        "where taskdate=#" & Format(Date, "mm/dd/yyyy") & "# order by id"
    Me.Queform.SetFocus
End Sub

 

[Voyager]

Hi arnelgp sir,
I love this. I really really mean it. Finally you resolved the query. Great.

 

[arnelgp]

glad it is working as you wanted it. cheers!