Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50

    Number 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)


    this is what I have so far:
    I noticed there was something in Excel called NETWORKDAYS?



    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]));

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    Oh darn sorry, but this is a separate issue

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    Not sure how it is different. You need to exclude weekend and evening calls?

    You have criteria for hours filter, now need one for the weekdays?

    Again, extract the date/time component from the data and use it in filter

    WHERE Weekday([Date & Time Left],2)<6

    The 2 parameter sets first day of week as Monday and the expression retrieves only the records matching days of week numbered 1-5.

    And instead of calculating elapsed hours, you now want elapsed minutes? This will require an adjustment of the DateDiff expression provided in other thread.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    ok here 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
    FROM [Message Tracking]
    GROUP BY [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned]
    HAVING ((([Message Tracking].[Date & Time Left]) Between [Begin_Date & Time Left] And [End_Date Returned]));


    and this is the result I got using a date range of 10/20/2014 6:00 AM TO 10/21/2014 (the [Date Returned] field is a Date/Time field which doesn't have the time in all the fields only the Dates so it isn't consistent like the [Date & Time Field]
    Date & Time Left Date Returned TotalHours
    10/20/14 6:32 AM 10/21/2014 18
    10/20/14 6:35 AM 10/21/2014 18
    10/20/14 6:38 AM 10/21/2014 18
    10/20/14 7:00 AM 10/21/2014 17
    10/20/14 7:06 AM 10/21/2014 17
    10/20/14 7:07 AM 10/21/2014 17
    10/20/14 7:10 AM 10/21/2014 17
    10/20/14 7:14 AM 10/21/2014 17
    10/20/14 7:19 AM 10/21/2014 17
    10/20/14 7:20 AM 10/21/2014 17
    10/20/14 7:21 AM 10/20/2014 2:00:00 PM 7
    10/20/14 7:21 AM 10/21/2014 17

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    If you don't have a time component, how do you plan to calculate elapsed time? I think Access defaults to midnight because midnight is 00:00:00 and Access just doesn't show the 00:00:00. So 10/21/2014 is actually midnight between 10/20 and 10/21.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    That's exactly my point

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    Then need to enter data correctly. Bad data in means bad data out. If there is no time specified, Access can only assume one thing - midnight.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    is there a way to automatically populate the time in the table for them, they are just entering in the date not the time. I can make it so that its a required field but can I make it so that if they only enter in the date it populates the time as well??

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    VBA code in BeforeUpdate event that looks at the value and if it has 00:00:00 time, cancel the update and return user to the box for data entry correction.

    Or maybe an input mask.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    So can I put 00:00:00 in the input mask and it will do the trick, I will try it

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    Would need date part in the input mask as well.

    Just hit me, 0s would be valid input for the time part of input mask. Any chance users will ever enter 00:00:00? Anyone work a night shift?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    no there is no night shift hours of operation is 6:00 AM
    4:30 PM

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,729
    Another approach might be ValidationRule property.

    Format([Date Returned], "hh:mm:ss")>"00:00:00"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    ok I put that into the data validation rule, but

    Format([Date Returned],"m/dd/yyyy h:nn:ss ")>"00:00:00"
    It wont allow me to not put in the time but it will let me get away with not putting in the date and I need both?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-05-2014, 03:44 PM
  2. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  3. Replies: 1
    Last Post: 08-08-2012, 01:42 PM
  4. Replies: 4
    Last Post: 08-06-2012, 10:25 AM
  5. Replies: 2
    Last Post: 03-02-2011, 01:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums