Access query counter per group

Question

Is it possible to add a sort per group (Sort/Group) field in an Access Query to have sort by value as per below table:

Note: ID is unique index (Auto Number)

Answer 1

On a real RDBMS, one would typically use the window function ROW_NUMBER for this.

select *
, row_number() over (partition by Group1 order by Value, ID) as Rownum
from yourtable

But an alternative is to use a correlated subquery.

select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value
        or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum
from yourtable t

Extra:

Simple tests to show the difference between ROW_NUMBER, RANK and DENSE_RANK

create table yourtable (
 ID int identity(1,1) primary key, 
 Group1 int, 
 Value int
);

insert into yourtable (Group1, Value) values
  (10,100),(10,150),(10,150),(10,150),(10,360)
, (200,360),(200,420),(200,420),(200,500),(200,500)

--
-- ROW_NUMBER (to get a sequence per group) 
--
select *
, ROW_NUMBER() OVER (PARTITION BY Group1 ORDER BY Value, ID) as Rownum
from yourtable
order by Group1, Rownum

ID | Group1 | Value | Rownum
-: | -----: | ----: | -----:
 1 |     10 |   100 |      1
 2 |     10 |   150 |      2
 3 |     10 |   150 |      3
 4 |     10 |   150 |      4
 5 |     10 |   360 |      5
 6 |    200 |   360 |      1
 7 |    200 |   420 |      2
 8 |    200 |   420 |      3
 9 |    200 |   500 |      4
10 |    200 |   500 |      5

--
-- Emulating ROW_NUMBER via a correlated sub-query
--
select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value 
        or (t2.Value = t.Value 
            and t2.ID <= t.ID))
) as Rownum
from yourtable t
order by Group1, Rownum

ID | Group1 | Value | Rownum
-: | -----: | ----: | -----:
 1 |     10 |   100 |      1
 2 |     10 |   150 |      2
 3 |     10 |   150 |      3
 4 |     10 |   150 |      4
 5 |     10 |   360 |      5
 6 |    200 |   360 |      1
 7 |    200 |   420 |      2
 8 |    200 |   420 |      3
 9 |    200 |   500 |      4
10 |    200 |   500 |      5

--
-- RANK (same values get same rank, but with gaps)
--
select *
, RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       5
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       4
10 |    200 |   500 |       4

--
-- Emulating RANK via a correlated sub-query
--
select *, 
(select count(t2.value)+1 from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value < t.Value) as Ranknum
from yourtable t
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       5
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       4
10 |    200 |   500 |       4

--
-- DENSE_RANK (same values get same rank, without gaps)
--
select *
, DENSE_RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       3
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       3
10 |    200 |   500 |       3

--
-- Emulating DENSE_RANK via a correlated sub-query
--
select *, 
(select count(distinct t2.Value) from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value <= t.Value
) as Ranknum
from yourtable t
order by Group1, Ranknum

ID | Group1 | Value | Ranknum
-: | -----: | ----: | ------:
 1 |     10 |   100 |       1
 2 |     10 |   150 |       2
 3 |     10 |   150 |       2
 4 |     10 |   150 |       2
 5 |     10 |   360 |       3
 6 |    200 |   360 |       1
 7 |    200 |   420 |       2
 8 |    200 |   420 |       2
 9 |    200 |   500 |       3
10 |    200 |   500 |       3

db<>fiddle here

Answer 2

Consider:

SELECT Data.ID, Data.Group1, Data.Value, 
DCount("*","Data","Group1='" & [Group1] & "' AND Value<" & [Value])+1 AS GrpSeq
FROM Data
ORDER BY Data.Value;

Or

SELECT Data.ID, Data.Group1, Data.Value, (
       SELECT Count(*) AS Cnt FROM Data AS Dupe 
       WHERE Dupe.Value<Data.Value AND Dupe.Group1=Data.Group1)+1 AS GrpSeq
FROM Data
ORDER BY Data.Value;