Results 1 to 6 of 6
  1. #1
    pdpeterson87 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    2

    Trying to find hour that has most attempts

    Hi,



    I am trying to find the "busy hour" which means the hour which has the most attempts. I feel like this should be simple but I am having a brain fart.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	9.3 KB 
ID:	13994

    I've attached an image to help explain but basically what I am trying to do is find the hour (0-23) where the maximum number of attempts occurs for each cell. For example if I had the following:
    Hour Cell Attempts
    0 1000 4
    1 1000 10
    2 1000 30
    3 1000 14
    0 2000 5
    1 2000 33
    2 2000 12
    3 2000 14

    I want to return the following:
    Hour Cell Attempts
    2 1000 30
    1 2000 33

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,538
    You will need to build an aggregate query that groups on the Cell Field and then the Hour Field and Sums on the Attempts field.

    Here is a link to help you get started.

    http://www.techonthenet.com/access/f...umeric/sum.php

  3. #3
    pdpeterson87 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    2
    Quote Originally Posted by alansidman View Post
    You will need to build an aggregate query that groups on the Cell Field and then the Hour Field and Sums on the Attempts field.

    Here is a link to help you get started.

    http://www.techonthenet.com/access/f...umeric/sum.php
    No. All that will do is sum the attempts of the sectors but I will still not know which hour has the highest total number of attempts. I'm pretty sure I will have to use a subquery after doing the aggregate sum query you described but I can't figure out how to pull out the highest hour from there.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    Sort the records on the aggregate Sum Descending.

    If you want to eliminate all other records, then use the TOP x parameter - still need the descending sort.
    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
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,538
    Don't include the sectors in your query. My response only mentions three fields and sectors was not one of them as was noted in your expected results example.

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Try something like this. All field and table names are made up, obviously.
    Code:
    SELECT 
      T1.MyDate, 
      T1.MyCell, 
      First(T1.MyHour), 
      First(T1.MyAttempts)
    FROM 
      MyTable AS T1,
      (SELECT T2.MyDate, T2.MyCell, Max(T2.MyAttempts) AS MaxAttempts
      FROM MyTable AS T2 
      GROUP BY T2.MyDate, T2.MyCell)
    WHERE 
       (T1.MyDate = T2.MyDate
    AND T1.MyCell = T2.MyCell
    AND T1.MyAttempts = MaxAttempts)
    GROUP BY T1.MyDate, T1.MyCell;
    I used First(MyHour) because it's likely that multiple hours will have the same number of attempts. If you want to retain the duplicates, then use something like this:
    Code:
    SELECT 
      T1.MyDate, 
      T1.MyCell, 
      T1.MyHour, 
      T1.MyAttempts
    FROM 
      MyTable AS T1,
      (SELECT T2.MyDate, T2.MyCell, Max(T2.MyAttempts) AS MaxAttempts
      FROM MyTable AS T2 
      GROUP BY T2.MyDate, T2.MyCell)
    WHERE 
       (T1.MyDate = T2.MyDate
    AND T1.MyCell = T2.MyCell
    AND T1.MyAttempts = MaxAttempts);

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

Similar Threads

  1. Replies: 11
    Last Post: 06-16-2014, 08:52 AM
  2. Replies: 1
    Last Post: 10-20-2013, 10:04 AM
  3. Replies: 13
    Last Post: 12-31-2012, 10:41 PM
  4. Form Seach Box-all attempts have failed.
    By leamas in forum Forms
    Replies: 12
    Last Post: 06-07-2012, 09:57 AM
  5. Time minus one for hour.
    By brianb in forum Queries
    Replies: 2
    Last Post: 03-09-2011, 11:02 AM

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