[AccessD] Simulating ROW_NUMBER in access

[Ryan W]

I've simulated ROW_NUMBER in Access, but I've come across a scenario where
I just can't get it to give the right results.

In this scenario I have a stored querydef where the date and time are
separate columns. When I add my correlated subquery (or Dcount), it does
not get the ordering right

Here's a fiddle with example data and the erroneous RowOrder column:
https://dbfiddle.uk/xdDhAPfM <https://dbfiddle.uk/y7bw_evm>

If you look at the fiddle, the first query after the table DML has RowOrder
all incorrect, the second query I composite the date and time fields and it
gets the right order.

The issue now is I can't get that syntax to work in the stored query with
the correlated subquery and compositing a date/time for comparison.

like this where statement in the subquery doesn't seem to work (the
counting is wrong or the syntax is wrong):

WHERE subquery.id = outerquery.id and subquery.sample_date & " " &
subquery.sample_time <= outerquery.sample_date & " " &
outerquery.sample_time