Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: # of calls during business hours

    Hello I've never had to do this before and I'm not actually sure how to explain it but here goes. I have to create a query that will reflect the number of calls and messages a person has either taken during business hours and if a message was left how long did it take them to return the message. We are open from 6:00AM - 4:30PM so my boss wants to only see how many calls a person took during business hours. now the users have to run this report daily and monthly. and have to make it accessible through a report so they can send it to the supervisors.
    The two fields I have to make the calculations on is the [Date & Time Left] the time the call was made [Date Returned] if they left a message the date it was returned. Also if the call was returned in one day she wants it broken down into minutes (as in how many hours and mins was the call returned) I noticed there was something in Excel called NETWORKDAYS?
    this is what I have so far:

    Code:
    SELECT [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], DateDiff("h",[Date & Time Left],[Date Returned]) AS TotalHours, [Message Tracking].ID, [Message Tracking].[User Returning]
    FROM [Message Tracking]
    GROUP BY [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], [Message Tracking].ID, [Message Tracking].[User Returning]
    HAVING ((([Message Tracking].[Date & Time Left]) Between [Begin_Date & Time Left] And [End_Date & Time Left]));
    Last edited by desireemm; 10-28-14 at 20:22.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Also if the call was returned in one day she wants it broken down into minutes (as in how many hours and mins was the call returned)
    ..do you mean returned within 24 hours or one the same day as the message was left?
    do you need the detail of each call/message or do you need a sumary

    lets assume you want the detail
    as its in a report you don't actually need to do anything special or clever within the query, providing you have the columns you need for your report. you casn do all of thsi within a report using soem code behind specific events in an Acces report. IF however you think the same essential data is required forsay more than one report and or form then doing it in a query makes more sense. remember the adagae of write once, run many times....

    presuming that if a message is only returned during working hours AND if the message is returned on a different day to the day it was left then you need to allocate time accordingly.

    lets also assume you are going to do this within the report NOT a query
    so lay out your report as you would normally.
    for now we are dealing with the reports detail
    add a hidden text box control to store
    the elapsed number of minutes, its datasource will be =iif( is NOT NULL(datereturned), datereutrned-datetaken,0)
    the elapsed number of working minutes. to calculate that you will need to call a function, called say CalcElapsedWorkingMinutes, its datasource will be = iif( is NOT NULL(datereturned), CalcElapsedWorkingMinutes(datetaken, datereutrned), 0 )
    OK so thats your time difference between the two events sorted. the IIF handles the case where the datereturned is null and if so sets the difference to 0. NOTE if you are going to do averages on this data then set it to NULL instead. this will allow the avg function to work correctly

    so in our two hidden text boxes you have the calendar elapsed and the working time elapsed difference in minutes. next you need to format that, again depends on how flexible you think this needs to be but I'd probably push this into a function

    Id probably add another hidden column which identifies the number of messages returned, called say NoRetruned, and set its datasource to =iif(is not null(datereturned),1,0)
    in a footer doing a COUNT on this control gives you the number of messages left, a SUM gives you the number of messages returned and therefere you can derive the number of messages not returned

    then in your group footer (if required, bear in mind a group footer can be a page and or a report footer
    Im guessing you'd want
    the number of calls left
    ... thats the COUNT of any control in your detail
    the number of calls returend
    ... thats the SUM of the control No Returned
    the average interval
    ...thats going to be
    https://support.office.com/en-us/art...3-b8ae9639fbf4
    ..or do it the hardocre way (SUM(elapsedTime)/count(NoReturnedCalls))*100

    I'll let you develop the functions . there are so many design decisions
    what to do if you have data out of range, what is a suitable default/return value if out of range. if it were me I'd set a default start time to be 06:00, a default end time to be 16:30, anything with invalid data gets those defaults on the grounds that it swill screw with their numbers and make people more attentive tot he data capture
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •