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 ?