Equivalent query in MS Access

Question

Following is the query written in ORACLE to give previous row , output given at last. How to frame below ORACLE query in MS Access specially ROWNUM part.. Appreciate your inputs

orcl> WITH d
  2       AS (SELECT ROWNUM rn,
  3                  dname,
  4                  deptno
  5           FROM   (SELECT dname,
  6                          deptno
  7                   FROM   dept
  8                   ORDER  BY dname)),
  9       e
 10       AS (SELECT ROWNUM + 1 rn,
 11                  dname,
 12                  deptno
 13           FROM   (SELECT dname,
 14                          deptno
 15                   FROM   dept
 16                   ORDER  BY dname))
 17  SELECT rn,
 18         d.dname,
 19         d.deptno this_row,
 20         e.deptno previous_row
 21  FROM   d
 22         left outer join e USING (rn)
 23  ORDER  BY dname;

    RN DNAME            THIS_ROW      previous_ROW

---

     1 ACCOUNTING             10
     2 OPERATIONS             40           10
     3 RESEARCH               20           40
     4 SALES                  30           20

Answer 1

Access SQL doesn't have direct counterparts for some of those Oracle features your query uses, so you can't "translate" it to Access. Instead you'll have to rewrite it from scratch.

You can use a DMax() expression to return the "previous" dname.

SELECT
    dname,
    deptno AS this_row,
    DMax("dname", "dept", "dname < '" & [dname] & "'")
        AS previous_dname
FROM dept;

That query returns this result set:

dname       this_row previous_dname
ACCOUNTING        10
OPERATIONS        40 ACCOUNTING
RESEARCH          20 OPERATIONS
SALES             30 RESEARCH

Then left join that result set back to the dept table, with the join based on previous_dname = dname.

SELECT
    d1.dname,
    d1.this_row,
    d2.deptno AS previous_row
FROM
    (
        SELECT
            dname,
            deptno AS this_row,
            DMax("dname", "dept", "dname < '" & [dname] & "'")
                AS previous_dname
        FROM dept
    ) AS d1
    LEFT JOIN dept AS d2
    ON d1.previous_dname = d2.dname
ORDER BY d1.dname;

I left out the rn (ROWNUM) column since it isn't needed for the join with this approach. However, if you still need/want it for some other reason, you can derive it with a DCount() expression.

SELECT
    d1.rn
    d1.dname,
    d1.this_row,
    d2.deptno AS previous_row
FROM
    (
        SELECT
            DCount("*", "dept", "dname <= '" & [dname] & "'")
                AS rn
            dname,
            deptno AS this_row,
            DMax("dname", "dept", "dname < '" & [dname] & "'")
                AS previous_dname
        FROM dept
    ) AS d1
    LEFT JOIN dept AS d2
    ON d1.previous_dname = d2.dname
ORDER BY d1.dname;