Group by, sort then add field based on counter or group

[ncub]

Just for example, I have a table where all records are un-grouped and un-sorted :

field_1 field_2 field_3
---------------------------
group_1 rec_1 aaaa
group_2 rec_1 eeee
group_3 rec_1 hhhh
group_1 rec_2 bbbb
group_1 rec_4 dddd
group_2 rec_2 kkkk
group_1 rec_3 cccc
group_2 rec_3 gggg
group_3 rec_3 nnnn
group_3 rec_2 oooo
group_3 rec_4 ssss


First I want it grouped by Field_1, then sort based on field_2 in each group.


After that, add a new field with value which will be change sequentially if met any of these condition :
for every 3 records if still in a same group
OR
when the group change

So it will become like this

field_1 field_2 field_3 field_4
------------------------------------
group_1 rec_1 aaaa 1
group_1 rec_2 bbbb 1
group_1 rec_3 cccc 1
group_1 rec_4 dddd 2
group_2 rec_1 eeee 3
group_2 rec_2 kkkk 3
group_2 rec_3 gggg 3
group_3 rec_1 hhhh 4
group_3 rec_2 oooo 4
group_3 rec_3 nnnn 4
group_3 rec_4 ssss 5

How to get that result with query in Access 2007 ?

[June7]

Review http://allenbrowne.com/ranking.html

[ncub]

I've read but still don't know how to implement it for my problem

Review http://allenbrowne.com/ranking.html

[June7]

Oh, just hit me - 'for every 3 records' as well as conditional on the group. That is unusual and I expect very difficult. Means first calculating a field that does a sequential count within each group - which can be done with a DCount(). Then calculate another field for the conditional numbering. Whether those two calcs can be combined into one I am not sure.

Domain aggregate functions can perform slowly in queries.

Alternatively, write complex VBA code that populates field in table.