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.