Assistance needed in getting the right calculation

[Voyager]

Hi Experts,
I need your suggestion in updating this code. Currently the code compares and suggests which task can be assigned to which employee by comparing Tasks with Employees till the specific employees available time (emp available) field which is in tasks table but due to needs
that field is moved to emp table and named avlbto.
1) Now how could I perform the same comparison?
2) Apart from that there is one more field that is added as avlblfrom to the emp table i.e emp5 who is available from 9 pm should be assigned for the tasks whose tasktime is after 9 pm in this case "Task13" ,"Task14" etc. but not
for tasks with tasktime earlier than 9 PM. How will I accomplish this? Could you suggest as you always did?

I have attached the db for reference code is attached to dashboard forms open event

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
    Dim intCount As Integer
    Dim intNextCount As Integer
    Dim arrEmp() As String
    Dim arrAvail() As Date
    
    ReDim arrEmp(0)
    ReDim arrAvail(0)
    Set rsTask = CurrentDb.OpenRecordset("select * from tasks " & _
        "where (taskdate is null) or (taskdate=#" & Format(Date, "mm/dd/yyyy") & "#) " & _
        "order by tasktime;")
    With rsTask
        .MoveFirst
        intNextCount = 1
        While Not .EOF
            If (!employee & "") <> "" And IsNull(![emp available]) = False Then
                ReDim Preserve arrEmp(UBound(arrEmp) + 1)
                ReDim Preserve arrAvail(UBound(arrAvail) + 1)
                arrEmp(UBound(arrEmp)) = !employee
                arrAvail(UBound(arrAvail)) = ![emp available]
            Else
                For intCount = intNextCount To UBound(arrEmp)
                    If arrAvail(intCount) > !tasktime Then
                        .Edit
                        !employee = arrEmp(intCount)

                        ![emp available] = arrAvail(intCount)
                        .Update
                        intNextCount = intNextCount + 1
                        If intNextCount > UBound(arrEmp) Then intNextCount = 1
                        Exit For
                    End If
                Next
            End If
            .MoveNext
        Wend
    End With
    
    Set Me.Queform.Form.Recordset = rsTask
    Set rsTask = Nothing
    Set rsTask2 = Nothing
    Set rsEmp = Nothing
                    
    Me.ests_subform.Form.RecordSource = "Select * from [ests]"
    Me.Queform.SetFocus
End Sub

 

[plog]

I think you are asking for a way to see what employees are available for what tasks. Right?

If so, you can create a query to determine that instead of looping through every task and every employee. First you will need a function to determine if a task time falls between start and end times that can span midnight. Below is a function to do that:

Function is_Available(in_Start, in_End, in_Between)
  ' determines if in_Between occurs between in_Start and in_End times
 
  ret = False
  ' return value, by default is false, will try and prove true
  
  dbl_Start = 1 * in_Start
  dbl_End = 1 * in_End
  dbl_Between = 1 * in_Between
  ' converting time values to decimals to make comparisons easier
  
  If (dbl_End < dbl_Start) Then
 ' timeframe spans midnight, will add 1 day to end time so can do simple comparison
    dbl_End = 1 + dbl_End
    If (dbl_Between < dbl_Start) Then dbl_Between = dbl_Between + 1
    ' between time is before start time, add 1 day to make sure it doesn't happen after midnight
  End If
  
  If (dbl_Between >= dbl_Start) And (dbl_Between <= dbl_End) Then ret = True
  ' does comparison to see if between time is between start and end
  
  is_Available = ret
 
End Function

Paste that into a module. Then the below SQL will identify which employees can do what tasks:

SELECT emp.EName, emp.avlbfrom, emp.avlto, tasks.Taskid, tasks.tasktime 
FROM emp, tasks
WHERE (((is_Available([avlbfrom],[avlto],[tasktime]))=True));

That query will list every task/employee that matches on timeframes.

 

[Voyager]

Hi Plog,
Thanks for your immediate reply. Code looks good. Please help me out in getting this sorted.
I can change the avlfrom and avlto fields in emp table to dd/mm/yy hh:nn format so that will it reduce the need of "Function is_Available"

Also can the SQL be modified to show only one employee for a task for e.g. my dashboard form shows emp1 for task1
So among the four employees emp1 who is free first will pick the task5 which will be first in the pending list.

 

[plog]

I can change the avlfrom and avlto fields in emp table to dd/mm/yy hh:nn format so that will it reduce the need of "Function is_Available"

That doesn't make a lot of sense. You do that and then this only works for 1 specific day per person. I thought it was about time, not days.

Also can the SQL be modified to show only one employee for a task

Perhaps, but now you are bringing a lot more tables and logic into this. Didn't really sign on for that. But if you can provide sample data to demonstrate what you want to occur I will look it over. Provide 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect to end up with after this process is run. Don't explain it, show me the data.

 

[Voyager]

Hi Plog,
Thanks for the assistance. I have given two screen shots for your reference one is how original status will look like another will how our output should look like.

 

[Voyager]

In this case
1) There are 6 employees
2) First 4 employees are given tasks e.g. Emp1 is having 1 hour (tasktime) worth work so he will be the first person to be free among the four at 1:15 (freeat)
3) so he will pick the first tasks with status pending ( task5) so he will be free after 5 hrs. i.e. 6:15 and so on
4)for emp3 he is available till 6PM but he will get a job which will end only at 21:10 so in the new field he will get the mark as "Pending"
5) for anyone this calculation should fall within their available time that is "avlfrom" and "avlto"that is why emp5 gets the task after 21:30

Am I clear, please let me know if you need any more details