Achieving Row_Number/Partition by in Ms Access

Achieving ROW_NUMBER / PARTITION BY in MS Access

In many cases we can achieve a similar result by performing an unequal self-join on the table and aggregating the results. For example, for data in a table named [MyData]

Ino  TYPE      DOC
---  --------  ---
  1  1800xxc1  3a 
  2  1810xxc2  3b 
  3  1700xxc3  3c 
  4  1700xxc4  3a 
  5  1800xxc5  3a 
  6  1800xxc6  3a 
  7  1800xxc7  3b 

the query

SELECT 
    t1.DOC,
    t1.TYPE,
    COUNT(*) AS [Ino Seq]
FROM 
    MyData AS t1
    INNER JOIN
    MyData AS t2
        ON t2.DOC = t1.DOC
            AND t2.Ino <= t1.Ino
GROUP BY
    t1.DOC,
    t1.TYPE
ORDER BY 1, 3

returns

DOC  TYPE      Ino Seq
---  --------  -------
3a   1800xxc1        1
3a   1700xxc4        2
3a   1800xxc5        3
3a   1800xxc6        4
3b   1810xxc2        1
3b   1800xxc7        2
3c   1700xxc3        1

ROW_NUMBER with PARTITION BY in MS Access

Use my RowCounter function. It takes a string as key, thus you can concatenate your first two fields as the key and call it like this:

SELECT RowCounter(CStr([CID]) & CStr([MPay]),False) AS RowID, *
FROM YourTable
WHERE (RowCounter(CStr([CID]) & CStr([MPay]),False) <> RowCounter("",True));

Of course, if a field is text, CStr is not needed, for example:

RowCounter(CStr([CID]) & [MPay],False)

MS Access - Row_Number Partition by

Need a unique record identifier - an autonumber field (ID in below query) should serve. Consider:

SELECT FX_Rates.Currency, FX_Rates.Exchange_Rate, FX_Rates.Date_of_Report, 
  (SELECT Count(*) AS HowMany
   FROM FX_Rates AS Dupe
   WHERE Dupe.ID < FX_Rates.ID AND Dupe.Currency=FX_Rates.Currency 
   AND Dupe.Exchange_Rate=FX_Rates.Exchange_Rate
   AND Dupe.Date_of_Report=FX_Rates.Date_of_Report)+1 AS GrpSeq
FROM FX_Rates;

For more info, review http://allenbrowne.com/ranking.html

Add a row number to a microsoft access query

One way to do it is with a correlated subquery:

SELECT s.pluquo,
              (SELECT COUNT(*) 
               FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS t
               WHERE t.pluquo <= s.pluquo
              ) AS row_number
FROM (
  SELECT DISTINCT pluquo
  FROM SYNC002_ACCESS
) AS s

Or with a self join:

SELECT s1.pluquo, COUNT(*) AS row_number
FROM (SELECT DISTINCT pluquo  FROM SYNC002_ACCESS) AS s1
INNER JOIN (SELECT DISTINCT pluquo  FROM SYNC002_ACCESS) AS s2
ON s2.pluquo  <= s1.pluquo 
GROUP BY s1.pluquo

access - row_number function?

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.

---

Related Topics