access - row_number function?

Question

I had this query, which gives me the desired results on postgres

SELECT
  t.*,
  ROW_NUMBER() OVER (PARTITION BY t."Internal_reference", t."Movement_date" ORDER BY t."Movement_date") AS "cnt"
FROM (SELECT
  "Internal_reference",
  MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
INNER JOIN dw."LO-D4_Movements" t
  ON t."Movement_date" = r.maxtime
  AND t."Internal_reference" = r."Internal_reference"

Issue is I have to translate the query above on Access where the analytical function does not exist ...

I used this answer to build the query below

SELECT 
      t."Internal_reference",
      t.from_code,
      t.to_code,
      t."Movement_date",
      t.shipment_number,
      t."PO_number",
      t."Quantity",
      t."Movement_value",
      t."Site",
      t."Import_date",
  COUNT(*) AS "cnt"
 FROM (
      SELECT "Internal_reference",
      MAX("Movement_date") AS maxtime
      FROM dw."LO-D4_Movements"
      GROUP BY "Internal_reference") r
LEFT OUTER JOIN dw."LO-D4_Movements" t
        ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
GROUP BY
      t.from_code,
      t.to_code,
      t."Movement_date",
      t.shipment_number,
      t."PO_number",
      t."Quantity",
      t."Movement_value",
      t."Site",
      t."Import_date",
      t."Internal_reference" 
ORDER BY t.from_code

Issue is I only have 1 in the cnt column.

I tried to tweak it by removing the internal_reference (see below)

SELECT 
      t.from_code,
      t.to_code,
      t."Movement_date",
      t.shipment_number,
      t."PO_number",
      t."Quantity",
      t."Movement_value",
      t."Site",
      t."Import_date",
  COUNT(*) AS "cnt"
 FROM (
      SELECT "Internal_reference",
      MAX("Movement_date") AS maxtime
      FROM dw."LO-D4_Movements"
      GROUP BY "Internal_reference") r
LEFT OUTER JOIN dw."LO-D4_Movements" t
        ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
GROUP BY
      t.from_code,
      t.to_code,
      t."Movement_date",
      t.shipment_number,
      t."PO_number",
      t."Quantity",
      t."Movement_value",
      t."Site",
      t."Import_date" 
ORDER BY t.from_code

However, the results are even worse. The cnt is growing but it gives me the wrong cnt

Any help are more than welcome as I'm slow losing my sanity.

Thanks

Edit: Please find the sqlfiddle

One option is to do a make table query and add an auto number field. — The Gambill, Mar 10 '17 at 14:59
Hi @TheGambill I'm only extracting data from that table, I'm unsure I want to create a new table and you need vba to create temp table in Access. — Andy K, Mar 10 '17 at 15:02
That makes sense... I'm thinking through it. Went to SQL Server long ago... A little rusty on access. — The Gambill, Mar 10 '17 at 15:08
Try changing count to dcount and see if it makes a difference for you. — The Gambill, Mar 10 '17 at 15:10
Can you create a [SQL Fiddle](http://sqlfiddle.com/) for your PostgreSQL query that shows some sample data and required results? — Gord Thompson, Mar 10 '17 at 18:18
Hi @GordThompson, please find the sqlfiddle in my question. I need to have the same results (see in the fiddle) but with an ANSI sql — Andy K, Mar 10 '17 at 18:41
Thanks for posting the fiddle. Access SQL is not ANSI SQL so you'll need to pick one. Also, your VBA comments above suggest that you won't be executing the query from within the Access UI, so how will you be executing it? ... via ODBC? ... OLEDB? .... — Gord Thompson, Mar 10 '17 at 18:58
@GordThompson Access SQL will be nice. I'm executing the query through the Access UI but I'm not skilled with VBA. However, if you have a step by step way of doing things, I'm all ear — Andy K, Mar 10 '17 at 21:49

score 1 · Accepted Answer · answered Mar 11 '17 at 14:32

I think Gordon-Linoff's code is close to what you want, but there are some typos I couldn't correct without a rewrite, so here's my attempt

SELECT
    t1.Internal_reference,
    t1.Movement_date,
    t1.PO_Number as Combination_Of_Columns_Which_Make_This_Unique,
    t1.Other_columns,
    Count(1) AS Cnt
FROM
    ([LO-D4_Movements] AS t1
    INNER JOIN [LO-D4_Movements] AS t2 ON
        t1.Internal_reference = t2.Internal_reference AND
        t1.Movement_date = t2.Movement_date)
    INNER JOIN (
        SELECT
            t3.Internal_reference,
            MAX(t3.Movement_date) AS Maxtime
        FROM
            [LO-D4_Movements] AS t3
        GROUP BY
            t3.Internal_reference
            )  AS r ON
        t1.Internal_reference = r.Internal_reference AND
        t1.Movement_date = r.Maxtime
WHERE  
    t1.PO_Number>=t2.PO_Number
GROUP BY
    t1.Internal_reference,
    t1.Movement_date,t1.PO_Number,
    t1.Other_columns
ORDER BY
    t1.Internal_reference,
    t1.Movement_date,
    Count(1);

In addition to within the max(movement_date) subquery, the main table is brought in twice. One version is the one for showing in your results, the other is for counting records to generate the sequence numbers.

Gordon said you need a unique id column for each row. And that's true if by "column" you mean to include derived columns also. Also it only needs to be unique within any combination of "internal_reference" and "Movement_date".

I've assumed, perhaps wrongly, that PO_Number will suffice. If not, concatenate with that (and some delimeters) other fields which will make it unique. The where clause will need updating to compare t1 and t2 for the "Combination of Columns which make this unique".

If, there is no appropriate combination available, I'm not sure it can be done without VBA and/or temp tables as The-Gambill suggested.

Gordon Linoff · Answer 2 · 2017-03-11T02:51:37.543

-1

This is a real pain in MS Access, as far as I know. One method is a correlated subquery, but you need a unique id column on each row:

SELECT t.*,
       (SELECT COUNT(*)
        FROM (SELECT "Internal_reference", MAX("Movement_date") AS maxtime
              FROM dw."LO-D4_Movements"
              GROUP BY "Internal_reference"
             ) as t2
        WHERE t2."Internal_reference" AND t."Internal_reference" AND

              t2."Movement_date" = t."Movement_date" AND
              t2.?? <= t.??
       ) as cnt
FROM (SELECT "Internal_reference", MAX("Movement_date") AS maxtime
      FROM dw."LO-D4_Movements"
      GROUP BY "Internal_reference"
     ) r INNER JOIN
     dw."LO-D4_Movements" t
     ON t."Movement_date" = r.maxtime AND
        t."Internal_reference" = r."Internal_reference";

The ?? is for the id or creation date or something to allow the counting of rows.

edited Mar 11 '17 at 02:51

answered Mar 10 '17 at 15:21

Gordon Linoff

1,242,037
58
646
786

Hi @gordon-linoff, thanks for your query but where the `t2` comes from ...? – Andy K Mar 10 '17 at 15:39
sorry @gordon-linoff your query is not appropriate in this case – Andy K Mar 10 '17 at 15:54
Why would this get an anonymous downvote? This is how you emulate `row_number()` in MS Access. – Gordon Linoff Mar 11 '17 at 14:00

access - row_number function?

2 Answers2