Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-09-2019, 07:08 AM   #1
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 95
Thanks: 87
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Assistance needed in getting the right calculation

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
Code:
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
Attached Files
File Type: zip Pickuptime.zip (281.1 KB, 23 views)

Voyager is offline   Reply With Quote
Old 01-09-2019, 07:43 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,239
Thanks: 10
Thanked 2,233 Times in 2,186 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Assistance needed in getting the right calculation

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:

Code:
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:

Code:
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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Voyager (01-10-2019)
Old 01-10-2019, 07:48 AM   #3
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 95
Thanks: 87
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Re: Assistance needed in getting the right calculation

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.

Voyager is offline   Reply With Quote
Old 01-10-2019, 07:56 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,239
Thanks: 10
Thanked 2,233 Times in 2,186 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Assistance needed in getting the right calculation

Quote:
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.

Quote:
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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Voyager (01-11-2019)
Old 01-11-2019, 09:06 AM   #5
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 95
Thanks: 87
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Re: Assistance needed in getting the right calculation

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.
Attached Files
File Type: zip after code.zip (449.7 KB, 21 views)
Voyager is offline   Reply With Quote
Old 01-11-2019, 09:13 AM   #6
Voyager
Newly Registered User
 
Join Date: Sep 2017
Posts: 95
Thanks: 87
Thanked 0 Times in 0 Posts
Voyager is on a distinguished road
Re: Assistance needed in getting the right calculation

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

Voyager is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
assistance needed daryln1243 Forms 5 05-08-2017 07:00 AM
Form Calculation Assistance chrisguk General 1 08-18-2011 03:42 AM
VBA Assistance Needed Please graviz Modules & VBA 4 12-21-2009 12:19 PM
Report Calculation Assistance Jakboi Reports 9 01-18-2007 12:04 PM
a little assistance needed soundsfishy Forms 12 06-25-2003 10:57 PM




All times are GMT -8. The time now is 10:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World