Achieving ROW_NUMBER / PARTITION BY in MS Access

Question

How can you make use of "Row number over partition by" in MS access? I googled it, but couldn't find any information as to how one would go about doing this. I'm talking about this particular function, which assigns a sequential integer to each row within the partition of a result set:

RowNumber Over(Partition by city Order By EmployeeID)

My data looks like this:

DOC_TYPE    Ino
3a  1800xxc1
3b  1810xxc2
3c  1700xxc3
3a  1700xxc4
3a  1800xxc5
3a  1800xxc6
3b  1800xxc7

However, I need it to look like this:

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

This is my query:

SELECT t1.RT_TAXCODE, t1.INV_NO, COUNT(*) AS Sno
FROM GroupByTAXCODE AS t1 INNER JOIN GroupByTAXCODE AS t2 ON (t2.RT_TAXCODE = t1.RT_TAXCODE) AND (t2.Inv_no <= t1.Inv_no)
GROUP BY t1.RT_TAXCODE, t1.INV_NO
HAVING COUNT(*)=1
ORDER BY 1, 3;

This is taking more time as f 30 seconds

You didn't find anything? If I Googled (access row_number over partition), the first link I find says that Access doesn't support this convention (originally from Oracle). But a few workarounds were provided. http://www.tek-tips.com/viewthread.cfm?qid=1615753 — Wayne G. Dunn, Feb 20 '14 at 20:20
Is [GroupByTAXCODE] itself a Query (as opposed to an actual Table)? — Gord Thompson, Feb 21 '14 at 14:05

score 17 · Accepted Answer · edited Mar 26 '21 at 00:40

17

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

edited Mar 26 '21 at 00:40

HansUp

95,961
11
77
135

answered Feb 20 '14 at 20:16

Gord Thompson

116,920
32
215
418

Im getting empty record ,My employee id is string and City also string – saikri Feb 20 '14 at 20:31
@saikri It shouldn't make any difference if your [EmployeeID] is `Text`. I suggest that you [edit](http://stackoverflow.com/posts/21917637/edit) your question to include some sample data and the query that you tried to use. – Gord Thompson Feb 20 '14 at 20:44
@saikri Please edit your question to show the query that you are actually using. – Gord Thompson Feb 20 '14 at 22:27
@saikri re: "I have 17000 records so taking so long time" - I just tested the query with 100,000 rows and it took about 8 seconds to run on my old test box. Are your execution times significantly longer than that? – Gord Thompson Feb 21 '14 at 11:49
Editted my query in question please have a look – saikri Feb 21 '14 at 13:24
I keep getting prompted to input the value for Ino – GilesDMiddleton Mar 02 '16 at 16:04
@PatrickHonorez - I just tried my query again (6+ years later) and it still works fine for me. What do you mean by "clearly non working"? – Gord Thompson Dec 14 '20 at 17:27

Achieving ROW_NUMBER / PARTITION BY in MS Access

1 Answers1

Linked

Related