Calculate first response in business hours.

[Carrie]

Once we find only the first outbound event for each SR (call). I will then link this query to another table that holds the actual call the customer made.

Here is how it works. A customer calls and we log their call (SR) then when the tech returns thier call and tries to fix this issue they document the call in an activity.

So, when I find the first outbound activity (we know it is the first because of the time it was created) I will link that to the table with all of the SR's.

I then need to somehow find the first response time. That is to say, how long did it take from the time the customer logged the call to the time the tech made thier first outbound activity.

I guess if we can go straight to this last part that would be beneficial.

I hope I haven't made things more confusing. I was actually trying to make them more clear.

This is the query I have so far:

SELECT [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #], Min([Tbl - Activities].Created) AS MinOfCreated
FROM [Tbl - Activities] INNER JOIN [Tbl - SR's] ON [Tbl - Activities].[SR #] = [Tbl - SR's].[SR #]
GROUP BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #]
ORDER BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #];

Please let me know if you need more information.

Oh, I almost forgot. The response time needs to be calculated in business hours (8:00a-5:00a; a 9 hour business day). Also, you should keep in mind that clients are able to log a call after business hours. This means that if a call is logged after business hours then the first response time start counting when the business day starts.

Sorry, I know this is long but I really appreciate your help. ray:

 

[Carrie]

Hello?

 

[CT Witter]

Take a look at the post http://www.mrexcel.com/board2/viewtopic.php?t=85533

See if that helps get you started.

CT

 

[Carrie]

Wow!...All I have to say is WOW!

I have no idea what all that code is. I have just gotten to the point where I know Excel pretty well. But Access?

I can do only basic things with Access and now I have this issue that needs to be resolved.

Anyway, something was said in that post about Excel Formula's. I think I have the formula to do this in Excel but how do I apply that to Access.

The formula is:

=MOD(B2,1)-MOD(A2,1)+(NETWORKDAYS(A2,B2,F2:F5)-1)*3/8

I see you are a Simpson's fan. Me too.

Favorite line: "To alcohol, the cause of...and solution to...all of life's problems."

 

[CT Witter]

While you can use excel formula's in access in would caution against it. If you copy in the code from the 4th response, this should get you started. In a query you can setup a field like:

ResponseTime: nhw([StartingDateTime],[EndingDateTime],[StartHour],[EndingHour])

You supply from your query the starting and ending dates/time and the start and end hour ( or these could just be set at "09:00", "17:00")

If you need help, send me your db with the query that returns the start date/time and end date/time and I can help you incorporate the code.

CT

 

[Carrie]

Ok...great I will try this.

I do have a couple of questions though.

Do I need to enter this code into Macros or Modules? I have never done this before so I don't know where or how to put it in.

The "Expression"??? that I put in there, the start date and time and the end date and time...do I refer to fields in a table? and then hardcode the start and end time?

What about holiday's? I have a table with our holidays on it.

 

[CT Witter]

You need to put the code in modules.

You are correct that you need to refer to the start and end date time from your table, the times can then be hard coded. If you look at my post I have given a ms kb article that shows how to determine holidays --Take a look at MS KB article #210064 - Determine If a Date Falls on a Weekend or on a Holiday in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;210064&Product=acc2000

I would suggest getting the code to work properly then incorporate the holiday code.

CT