update query - counting # records of id by time

[Guest]

I have a db with fields: id date/time. I am looking to create a count where
if it's the first date/time for an id, it states 1, then if second, 2 and so
on and so forth. I'm having trouble getting count to do this b/c of keeping
the date/time variable in there as a sorting variable - I just get 1. How
can I do this?

 

[Guest]

Try this ---
SELECT ID, UrDateTime, (SELECT COUNT(*)
FROM [alison77] T1
WHERE T1.ID = T.ID
AND T1.UrDateTime <= T.UrDateTime) AS Rank
FROM [alison77] AS T
ORDER BY ID, UrDateTime;