Time taken Query

[tucker61]

Attached a cut down version of my database.

I want to monitor the time taken that a job is left open at a certain status (who is assigned to job).

All jobs should begin with a Status of 1, and End with a status of 8. In between the job could go through different status's multiple times. So i have been asked can i run a query that shows how long a job is at a certain status (For example status 2) So i have a query that brings back the Min time the status was changed to 2, but don't know how to write the query that shows when it was changed from 2 to 3 or 4 etc.

Any help appreciated.

 

[isladogs]

I'm not sure I understand what you're asking.
If you remove the criteria '2' in Status_ID field, does that give you what you want?

 

[tucker61]

No not really. I would like to see over a month the average time taken to process at each stage. Or between 2 status' s

 

[isladogs]

Can you mock up some results based on your data e.g. in Excel and post them in order to clarify what you want

 

[tucker61]

I suppose the easiest way to explain it is that i am after the time difference from a job being status 2, to it being status 3 etc. as in column "e" on the enclosed.

 

[isladogs]

I'm about to logout for a while though someone else may step in

In the meantime there are several #DIV/0! errors in your Excel file
Can you please fix & re-post

 

[The_Doc_Man]

In between the job could go through different status's multiple times.

This little "gotcha" can make life difficult because you will need something that we normally don't recommend. But in this case, it might be required. Since it appears that you can return to a status (or at least that is how I interpret your comment), that means that you have a clear requirement to do a "divide and conquer" operation.

You want to do two disparate things. You don't appear to have everything you need for one of those things. It is also not clear as to how far you want to take this. But here goes...

Your data shows a job ID, date/time of status change, the new status, and (presumably) the person who update the status. For what you said you wanted to do, that's not enough. You need another field that shows elapsed time in current status (or equally, elapsed time in prior status).

First, add a field in the table: TimeInStatus - Date

Second, whatever you are using for status updates (presumably a form we didn't see?), have it look at the current time and the entry's previous status change time and compute/store the time in status.

Third, your query can then do a group-by of JOB_ID and STATUS_ID and a SUM of the TimeInStatus field to get a total time for each status.

This is an example of Old Programmer's Rule #2: Access can't tell you anything you didn't tell it first (or tell it how to compute it). You had no way of computing the individual time-in-status because you had no place to put it.

 

[tucker61]

Makes sense what you are both saying... I have updated the excel file, and used the days 360 formula to calculate the days in the status, then used the averageif formula to give me the average.

 

[plog]

So you are starting with this data:

Job_ID, Status_Change, User_Name, Status_ID
0000001, 3/16/2015 8:12:57 AM, Nic, 1
0000001, 3/16/2015 8:16:12 AM, Nic, 2
0000001, 3/16/2015 9:49:10 AM, Nic, 2
0000001, 3/17/2015 9:54:55 AM, Joe, 3
0000001, 3/17/2015 9:55:19 AM, Joe, 2
0000001, 3/17/2015 9:55:29 AM, Joe, 3
0000001, 3/17/2015 10:32:20 AM, Joe, 2
0000001, 3/17/2015 10:32:26 AM, Joe, 3
0000001, 3/17/2015 11:26:14 AM, Nic, 2
0000001, 3/17/2015 11:35:01 AM, Les, 8


Now show me what you hope to end up with. Show me the exact data along with field names you want in the final result.

 

[tucker61]

I want to see
Status ID, Average Days
1 5
2 3
3 9
etc..

 

[plog]

So it is regardless of Job_ID and User_Name? Also, the increments you want to use is Days? That means round up to one day if the result is 12 hours?

Also your data doesn't jive. The example data I gave you was not in Status_ID=1 for 5 days. Please show me exactly what you want based on the sample data I showed..

 

[tucker61]

So it is regardless of Job_ID and User_Name? Also, the increments you want to use is Days? That means round up to one day if the result is 12 hours?

Also your data doesn't jive. The example data I gave you was not in Status_ID=1 for 5 days. Please show me exactly what you want based on the sample data I showed..

Don't really want to round up, I suppose if it has been answered on the same date, I would class that as zero, if is is dealt with in the next date, I would class as 1. Week ends might be a issue

Sent from my SM-T715 using Tapatalk

 

[tucker61]

This little "gotcha" can make life difficult because you will need something that we normally don't recommend. But in this case, it might be required. Since it appears that you can return to a status (or at least that is how I interpret your comment), that means that you have a clear requirement to do a "divide and conquer" operation.

You want to do two disparate things. You don't appear to have everything you need for one of those things. It is also not clear as to how far you want to take this. But here goes...

Your data shows a job ID, date/time of status change, the new status, and (presumably) the person who update the status. For what you said you wanted to do, that's not enough. You need another field that shows elapsed time in current status (or equally, elapsed time in prior status).

First, add a field in the table: TimeInStatus - Date

Second, whatever you are using for status updates (presumably a form we didn't see?), have it look at the current time and the entry's previous status change time and compute/store the time in status.

Third, your query can then do a group-by of JOB_ID and STATUS_ID and a SUM of the TimeInStatus field to get a total time for each status.

This is an example of Old Programmer's Rule #2: Access can't tell you anything you didn't tell it first (or tell it how to compute it). You had no way of computing the individual time-in-status because you had no place to put it.

If Instead of change status if I have 2 fields, status start, and status end. Then I could compare the date between the 2 fields?

So from moving from status 1 to 2, the status end of status 1 would be the same date as the start on status 2..

Sent from my SM-T715 using Tapatalk

 

[plog]

Ok, back to my original request. Using the data below, show me exactly what data you want returned:

Job_ID, Status_Change, User_Name, Status_ID
0000001, 3/16/2015 8:12:57 AM, Nic, 1
0000001, 3/16/2015 8:16:12 AM, Nic, 2
0000001, 3/16/2015 9:49:10 AM, Nic, 2
0000001, 3/17/2015 9:54:55 AM, Joe, 3
0000001, 3/17/2015 9:55:19 AM, Joe, 2
0000001, 3/17/2015 9:55:29 AM, Joe, 3
0000001, 3/17/2015 10:32:20 AM, Joe, 2
0000001, 3/17/2015 10:32:26 AM, Joe, 3
0000001, 3/17/2015 11:26:14 AM, Nic, 2
0000001, 3/17/2015 11:35:01 AM, Les, 8

Include field names.

 

[tucker61]

I want to see how long a job was in a status before it was moved to the next status.

So for job_id 00000001, it went into status_id "2" on 3/16/2015(status_change) , and then went to status_ID "3" on 3/17/2015, so was in status 2 for 1 day...

I have now added a additional field Called Date_Changed, So now when the status is changed, it triggers the below Code, which looks at the Max_date in the status_change field, for job number 1, then updated the date_changed field with Now(), then I should be able to use DateDiff to get the number of days, but I am now getting a syntax error.

Error 3075, Missing Operator in query expression 'Job_id = 1 AND Status_change = 17/03/2015 11:35:01'.

This is more than probably to do with either a apostrophe or Speech marks. But I cannot work it out

Function InsertStatus()
Dim mysql As String
Dim tbjobid As Double
tbjobid = 1
Max_date = Nz(DLookup("Status_Change", "tblQCJobStatus", "Job_ID=" & Nz(tbjobid, 0) & " AND Status_Change=" & SQLDate(Nz(DMax("Status_Change", "tblQCJobStatus", "Job_ID=" & Nz(tbjobid, 0)), "1/1/1"))), 0)
mysql = "UPDATE tblqcjobstatus SET Date_Changed = " & SQLDate(Now) & " WHERE Job_id = " & tbjobid & " AND Status_Change = " & Max_date & " ;"
CurrentDb.Execute mysql
End Function

 

[plog]

Well, if you ever want to give me what I've asked for I would be happy to help. Until then, hopefully someone else can assist you.