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

    total calls per hour

    hello I need to create a report that will show the total calls per hours
    I have the select statement that I am using below. Was wondering in the report do I group by ID, because in the query I get dups and I just want to see 12AM, or 6pm or 1pm and the total calls that came in at that time
    example:

    6am -20


    12pm -30

    Code:
    SELECT [Message Tracking].Caller, [Message Tracking].[Customer Type], [Message Tracking].[Date Returned], [Message Tracking].ID, [Message Tracking].[Request ID], Format([Message Tracking].[Date & Time Left],"hh ampm") AS [Hour], Count(*) AS Calls
    FROM [Message Tracking]
    WHERE ((([Message Tracking].[Date & Time Left]) Between [beginning_Date & Time Left] And [end_Date & Time Left]))
    GROUP BY [Message Tracking].Caller, [Message Tracking].[Customer Type], [Message Tracking].[Date Returned], [Message Tracking].ID, [Message Tracking].[Request ID], Format([Message Tracking].[Date & Time Left],"hh ampm");

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Remove ID from the query.

    Instead of grouping and summing in query, consider a report using Grouping & Sorting features with aggregate calcs in footers. This will allow display of detail data as well as summary calcs.
    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
    Worked perfect thank you, one more question please. How do I sort by AM and PM?? I need the AM first then the PM

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If Sort on the formatted time field won't work in the aggregate query, might have to build another query that uses the aggregate query.

    Or consider the suggestion to build a report instead of the aggregate query to start with.
    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
    this is all on the report not the query this is how its sorting

    12 PM
    11 AM
    10 AM

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like Descending order.
    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
    I was thinking of incorporating this Order by into my select statement


    Code:
    ORDER  BY 
               IIF([Message Tracking].[Hour] = '6AM',0,1), 
               IIF([Message Tracking].[Hour] = '7AM',0,1), 
               IIF([Message Tracking].[Hour] = '8AM',0,1), 
               IIF([Message Tracking].[Hour] = '9AM',0,1), 
               IIF([Message Tracking].[Hour] = '10AM',0,1), 
               IIF([Message Tracking].[Hour] = '11AM',0,1), 
        IIF([Message Tracking].[Hour] = '12PM',0,1));


    SELECT STATMENT

    Code:
    SELECT Format([Message Tracking].[Date & Time Left],"hh ampm") AS [Hour], Count(*) AS Calls, [Message Tracking].[Date & Time Left], [Message Tracking].[Date & Time Left]
    FROM [Message Tracking]
    GROUP BY Format([Message Tracking].[Date & Time Left],"hh ampm"), [Message Tracking].[Date & Time Left], [Message Tracking].Caller, [Message Tracking].[Customer Type]
    HAVING ((([Message Tracking].[Date & Time Left]) Between [beginning_Date & Time Left] And [end_Date & Time Left]))
    ORDER BY Format([Message Tracking].[Date & Time Left],"hh ampm") DESC;

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't think the IIf() will work. At least it doesn't make any sense to me.

    The query has DESC parameter. If you want ascending then remove the DESC.
    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
    I put it in ASC order the problem is when I do its starts at

    12PM
    11AM
    10 AM

    My boss wants it in a certain order, we start at 6AM so she wants the sort order to start at


    6AM
    7AM
    9AM
    10AM
    11AM
    12PM

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ooops, sorry, forgot that the alpha sort will throw this sort out of whack. What you need is 24-hr clock output. Use a different calc for the sorting:

    ORDER BY Format([Date & Time Left], "hh")

    Format produces a string output. An actual number may be better:

    ORDER BY Hour([Date & Time Left])
    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
    hello this is what I have and its producing the right results


    The only problem is my boss wants it sorted by the work hours we are open from 6AM -4:30PM
    so order needs to be
    6AM
    7AM
    8AM
    9AM
    10AM
    11AM
    12PM

    When I run the query this is what I get

    1PM
    3PM
    4PM
    5AM
    6AM
    7AM
    8AM
    9AM
    10AM
    11AM
    12PM



    Code:
    SELECT Format([Message Tracking].[Date & Time Left],"hh ampm") AS [Hour], Count(*) AS Calls, [Message Tracking].[Date & Time Left], [Message Tracking].[Date & Time Left]
    FROM [Message Tracking]
    GROUP BY Format([Message Tracking].[Date & Time Left],"hh ampm"), [Message Tracking].[Date & Time Left], [Message Tracking].Caller, [Message Tracking].[Customer Type]
    HAVING ((([Message Tracking].[Date & Time Left]) Between [beginning_Date & Time Left] And [end_Date & Time Left]))
    ORDER BY Format([Message Tracking].[Date & Time Left],"hh ampm");

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not using the suggestion in my previous post.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL SERVER ADO Calls.
    By majero in forum SQL Server
    Replies: 3
    Last Post: 05-28-2015, 12:07 PM
  2. Replies: 6
    Last Post: 07-25-2014, 09:25 AM
  3. Tracking calls
    By stange154 in forum Database Design
    Replies: 1
    Last Post: 02-20-2014, 09:49 AM
  4. Replies: 1
    Last Post: 10-20-2013, 10:04 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