ROW_NUMBER with PARTITION BY in MS Access

Question

How to achieve the Row number over partition by in the MS access .I Google but cant find information can you please how to do this. My Data looks like this

CID MPay    NumGrp
4   139608  1
4   139609  2
4   139610  3
4   139611  4
5   139608  1
5   139609  2
5   139610  3
6   139607  1
6   139608  2
6   139609  3
6   139610  4
6   139611  5

To this output, showing:

  CID   MPay    NumGrp     ID
    4   139608  1          1
    4   139609  2          2
    4   139610  3          3
    4   139611  4          4
    5   139608  1          5
    5   139609  2          6
    5   139610  3          7
    6   139607  1          8
    6   139608  2          9
    6   139609  3          10
    6   139610  4          11
    6   139611  5          12

BEST REGARDS and many thanks for you great help.

I don't see any partitioning in the desired output, just an added row number. June7's dupe should hold all the answers in that case. — Erik A, Jan 21 '18 at 21:06

score 3 · Accepted Answer · answered Jan 22 '18 at 06:59

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)

ROW_NUMBER with PARTITION BY in MS Access

1 Answers1