[AccessD] Simulating ROW_NUMBER in access

Ryan W wrwehler at gmail.com
Thu Apr 13 11:07:16 CDT 2023


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


More information about the AccessD mailing list