[AccessD] Simulating ROW_NUMBER in access

[Rocky Smolin]

I don't know by looking at where the disconnect is, but I would modify your
data by appending 1, 2, 3, 4 etc., after each data element in your insert
so you can see which column it's actually being sorted on, making sure that
each row's data has different numbers appended. Then you can see which row
is actually getting the sort, if any, and that might give you a clue.

BTW I have had some hair pulling incidents over the years with queries that
work perfectly in the QBE, but when I copy the SQL out and paste it into my
module, the query fails.

Hard to recall but I think in most cases I had to engineer a workaround.

r

On Thu, Apr 13, 2023 at 9:07 AM Ryan W <wrwehler at gmail.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>