Ranking Records Chronologically by Day/Person

[tairevolt]

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"):

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"):

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!

[June7]

Review http://allenbrowne.com/ranking.html

[Dal Jeanis]

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?

[Dal Jeanis]

Assuming you had this organization on the underlying table:

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:

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:

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:

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.

[tairevolt]

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.

[Dal Jeanis]

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.