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

    Ranking Records Chronologically by Day/Person

    Hey all! I'm trying to rank a series of times (i.e., time of day, not duration of time) but am having some trouble with getting the rank to reset every day and group by person. The times represent each time an event happens to a person.



    I have a table titled "203" that contains records of each time an event happens to a person. I have queries.

    Here's the first query (titled "Time_Select"):

    Code:
    SELECT [203].Name, [203].Location, [203].Date, [203].Time, Sum([203].Time) AS TimeSum
    FROM 203
    GROUP BY [203].Name, [203].Location, [203].Date, [203].Time;
    Here's the second query (titled "Time_Rank"):

    Code:
    SELECT Time_Select.Location, Time_Select.Date, Time_Select.Name, Time_Select.Timesum AS Expr1, (SELECT Count(*) FROM Time_Select AS I WHERE I.TimeSum > Time_Select.Timesum)+1 AS Rank
    FROM Time_Select
    GROUP BY Time_Select.Location, Time_Select.Date, Time_Select.Name, Time_Select.Timesum, Time_Select.Time
    ORDER BY Time_Select.Timesum DESC;
    My goal is to have something like this:

    Name Location Date Time Rank
    John 100 1/1/2013 11:00 1
    Mary 100 1/1/2013 11:15 1
    John 100 1/1/2013 12:00 2
    John 100 1/1/2013 12:10 3
    John 100 1/2/2013 12:30 1










    Currently, the Time_Rank query is calculating the ranks above as 1, 2, 3, 4, 5. Any suggestions for what I'm doing wrong in the queries? Also any help formatting the time column to hours:minutes (I've tried using FORMAT, embedding the expression with "hh:nn AMPM" and I keep getting errors). Thank you!
    Last edited by tairevolt; 12-11-2013 at 01:04 PM. Reason: adding code tags

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    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
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) I don't understand why you're summing the time. What would that mean?

    2) Is the GROUP BY actually needed? Will there be more than one record at a particular date and time?

    3) Is there a reason to break up the timestamp into separate date field and time fields?

    4) Time, Date, Name are reserved words.

    5) 203 is a scary name for a table. It could be interpreted as a number...?

    6) Are you looking to count how many times it happened to a person in a single location, on a single day, or across the entire query, in one location, or across the entire query, in any location?

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Assuming you had this organization on the underlying table:
    Code:
    tbl203
       MyName      Text
       MyLocation  Text
       MyDate      Date/Time
       MyTime      Date/Time
    And assuming that there will only be one record for each date and time stamp, then you use this underlying query:
    Code:
    qryTimeSelect:
    SELECT 
       T1.MyName, 
       T1.MyLocation, 
       (T1.MyDate + T1.MyTime) AS MyDateTime
    FROM 
       tbl203 AS T1;
    And then you get your ranks by joining the query back to itself with a less than or equal to:
    Code:
    SELECT 
       Q1.MyName, 
       Q1.MyLocation, 
       Q1.MyDateTime,
       Format(Q1.MyDateTime,"Short Date") As MyDate,
       Format(Q1.MyDateTime,"HH:nn AMPM") As MyTime,
       Count (Q2.MyDateTime) As Rank
    FROM 
       qryTimeSelect AS Q1
       INNER JOIN  
       qryTimeSelect AS Q2
       ON ((Q1.MyLocation = Q2.MyLocation)
       AND (Q1.MyName = Q2.MyName))
    WHERE 
      Q2.MyDateTime <= Q1.MyDateTime
    GROUP BY 
       Q1.MyName, 
       Q1.MyLocation, 
       Q1.MyDateTime;
    If you wanted rank regardless of location, then you would do this:
    Code:
    SELECT 
       Q1.MyName, 
       First(Q1.MyLocation) As MyLocation, 
       Q1.MyDateTime,
       Format(Q1.MyDateTime,"Short Date") As MyDate,
       Format(Q1.MyDateTime,"HH:nn AMPM") As MyTime,
       Count (Q2.MyDateTime) As Rank
    FROM 
       qryTimeSelect AS Q1
       INNER JOIN  
       qryTimeSelect AS Q2
       ON (Q1.MyName = Q2.MyName)
    WHERE 
      Q2.MyDateTime <= Q1.MyDateTime
    GROUP BY 
       Q1.MyName, 
       Q1.MyDateTime;
    Once you know that the queries above are working, you can drop MyDateTime from the final SELECT list, but leave it in the GROUP BY.

    As you can see above, you don't need to include a field in the GROUP By to get a field into the output. You just use First() or Last() or Avg() or Max() or Min() as appropriate. If the values are all the same for a group, then any of them will work.

  5. #5
    tairevolt is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    2
    Dal your suggestions worked fantastically! To answer your questions:

    1) I don't understand why you're summing the time. What would that mean? I originally tried to use count, but this didn't work the way I had it, so I tried sum.

    2) Is the GROUP BY actually needed? Will there be more than one record at a particular date and time? I think your suggestion handled this the right way.

    3) Is there a reason to break up the timestamp into separate date field and time fields? The time actually incorporates the date in it, so 8:00 is actually 1/1/2013 8:00. The date is there for visual reference when using the table, but perhaps this is unnecessary?

    4) Time, Date, Name are reserved words.

    5) 203 is a scary name for a table. It could be interpreted as a number...? Is this a bad thing?

    6) Are you looking to count how many times it happened to a person in a single location, on a single day, or across the entire query, in one location, or across the entire query, in any location? Across the entire query in one location.
    Last edited by tairevolt; 12-12-2013 at 11:51 AM. Reason: accidentally hit enter

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1,2) OK.

    3) Okay, be aware that a single Date/Time field can store both the date and the time in a single field, and you can get the part you want with the format() function. Or, you can store the date in one field and the time in a second field, and merge them by adding the two fields. In this case, since someone might have a swing shift that rolls over to the next day, merging the two fields -- in the query, at least -- allowed a single comparison to test the "less than or equal to" condition, and establish the right order for the records.

    5) Very bad. Not good. These are mere opinions and value judgments. If there is a valid reason for that being exactly the right name, then don't change it. Look for threads on naming standards. You probably won't find any that tell you not to give a table a plain number for a name, but that's because names are usually descriptive, so very few people would have started with that as a name. Best to add tbl at the front, at the very least.

    6) Then you have what you need.

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

Similar Threads

  1. Help with Ranking
    By Mbithy in forum Queries
    Replies: 3
    Last Post: 08-05-2012, 10:21 PM
  2. Ranking
    By mrbabji in forum Queries
    Replies: 1
    Last Post: 04-19-2012, 10:33 AM
  3. Ranking (Look for previous ranking)
    By leobear in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 05:58 PM
  4. Replies: 1
    Last Post: 01-28-2011, 06:58 AM
  5. Help to build a ranking
    By chorbi26 in forum Access
    Replies: 0
    Last Post: 12-20-2010, 12:47 PM

Tags for this Thread

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