I have the data set example in the table below.
I would like to create a Function that allows me to use the function in an Access Query that will determine the leadtime in WORKING DAYS between the DATE_COMPLETED in one record vs the CDATE_COMPLETED of the next record. I would need to order the tasks by the TASK_RECORD_ORDER within a grouping of the MODEL_SKU_NUMBER.
Example....
For MODEL_SKU_NUMBER ABC123, order the tasks by the TASK_RECORD_ORDER, find the DATE_COMPLETED of Step 1 and the CompletionDate of Step 2, log the DELTA IN WORKING DAYS (M-F) between the two dates as the LEADTIME for Step 2. Step 1 will not have a Leadtime and any Tasks that have a blank for either DATE_COMPLETED will not have Leadtime.
There are instances where the tasks could be completed out of order so you might get a negative Leadtime. That would be OK as the negative would indicate our of order and I can figure out if I exclude them or not, later.
I'm thinking this is a function where I place the Function into a Query Field vs pulling a data set and having it run through the data set and store the data in another table. But I'm open to suggestions.
Before anyone wonders....yes I could do this in excel but I was hoping to create a dynamic query that will run daily and constantly provide updated data as steps are completed or models added.
Thanks!
MODEL_SKU_NUMBER TASK_RECORD_ORDER DATE_COMPLETED LEADTIME XYZ-123 0 7/11/2017 XYZ-123 1 7/24/2017 10 XYZ-123 2 8/3/2017 9 XYZ-123 3 7/31/2017 -4 XYZ-123 4 10/24/2017 62 XYZ-123 5 8/3/2017 -59 XYZ-123 6 8/3/2017 1 XYZ-123 7 XYZ-123 8 8/25/2017 XYZ-123 9 8/16/2017 -8 XYZ-123 10 8/17/2017 2 XYZ-123 11 8/25/2017 7 XYZ-123 12 8/24/2017 -2