Achieving ROW_NUMBER / PARTITION BY in MS Access

by

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

More Related Contents:

  1. How to simulate UNPIVOT in Access?
  2. Top n records per group sql in access
  3. CONCAT equivalent in MS Access
  4. sql in ms-access [closed]
  5. Managing and debugging SQL queries in MS Access
  6. SQL multiple join statement
  7. How do I write a full outer join query in access
  8. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  9. How to do INSERT into a table records extracted from another table
  10. Multiple INNER JOIN SQL ACCESS
  11. Table-less UNION query in MS Access (Jet/ACE)
  12. Ms Access Query: Concatenating Rows through a query
  13. is there a group_concat function in ms-access?
  14. How do I implement pagination in SQL for MS Access?
  15. [] brackets in sql statements
  16. Row numbers in query result using Microsoft Access
  17. SQL to add column with default value – Access 2003
  18. How can I implement SQL INTERSECT and MINUS operations in MS Access
  19. Lightweight SQL database which doesn’t require installation [closed]
  20. Convert String to Date in MS Access Query
  21. Access 2010: Syntax error (missing operator) in query expression
  22. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  23. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  24. Editing Record issues in Access / SQL (Write Conflict)
  25. How do I create a table alias in MySQL
  26. How to save the result of a SQL query into a variable in VBA?
  27. Select query with date condition
  28. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  29. Microsoft Access – Case Query
  30. Check if access table exists

Leave a Comment