without use of an ordered ID field
Note the list cannot have an ordered ID.
Be exceedingly careful here. You are asking for something that technically does not exist in nature.
Let me explain. SQL is based on SET theory. SET theory does not have the concept of ORDER (with respect to the order of appearance of members). There IS no "NEXT" or "PREVIOUS" in terms of SET theory.
You MUST impose an order externally if this question is to make any sense at all. But you
can impose the order via a query that looks at the very time fields you wanted to examine without creating a new record ID.
Now, there is a way to do what you want via VBA. The VBA would be run either through a form (in the form's class module run by an EVENT-based trigger) or a macro (from a general module using RunCode). Why VBA (you ask....)? Because SQL has no syntax for relative order of records. There IS no NEXT or PREVIOUS operator.
So... how to get there from here? With a query that imposes time-order on your dataset, you are halfway there. Write the VBA code to open the query's recordset once (perhaps as a dynaset, which you would look up using the Access Help Files). A dynaset
can be updated from VBA code.
Then in a VBA loop that looks at each record in the query, you would store the current position by capturing the BOOKMARK propery of the recordset (q.v. in the Access Help Files). This would allow you to return to the bookmark later if you needed to. You can make a recordset clone (which you can look up in the help files) to have two streams in the same recordset.
You can use the BOOKMARK, .MOVENEXT, and .MOVEPREV methods on the recordset - which works in VBA because VBA is NOT an SQL entity. IN THAT CONTEXT, there is such a thing as capturing the data you want to keep, stepping to another record, and doing a comparison. Because VBA is OUTSIDE of the actions normally allowed to SQL.
I'll add one more bit of clarification. In the original derivation of the laws of set theory that led to creation of SQL, the implementation was left to the vendor but the idea was that when an SQL statement was complete, all records in the set were treated in the same way. Inherent in that historical setup was that the user of SQL could not see and should not care in what order a particular set of actions occurred, which is the point that justifies omitting NEXT and PREVIOUS keywords. To you, SQL is a monolithic, all-records-at-once operation. Which is why you need to use something like VBA that can get into the vendor's implementation by low-level methods.
