accessing the previous line in the query

[vishv.kemonte]

Hi, everyone!There is a table with two columns

ст1	ст2
Кон1	1
Кон1	2
Кон1	5
Кон1	6
Кон1	9
Кон2	1
Кон2	2
Кон2	5
Кон2	6
Кон2	9

I want to make groups in the query.The condition for creating a group is simple: sorting by column 1 and column 2. If the value of column 1 differs from the previous value of column 1, then the group number increases by 1 compared to the group of the previous row. Also, if the value of column 2 is greater than the previous value of column 2 by more than 1, then the group number is also increased by 1 compared to the group of the previous row. As a result, you will get the following:

ст1	ст2	Gr
Кон1	1	1
Кон1	2	1
Кон1	5	2
Кон1	6	2
Кон1	9	3
Кон2	1	4
Кон2	2	4
Кон2	5	5
Кон2	6	5
Кон2	9	6

Is it possible to do using just query without VBA?

[CJ_London]

using pure sql, possibly but it would be very complicated. It is easy enough to find the previous record, but the problem you have is the rules for setting the grouping value.

You also need to be careful with your terminology. Databases do not have any concept of 'previous' unless an order is specified. Your data is conveniently in ascending order of ct1 and ct2 - but what if the 2nd and 3rd records are swapped?

I will assume 'previous' means when sorted by ct1 and ct2.

For these situations I use a function which can be used in a query. For your situation, it might look like this

Function CTGroup(Optional t As Variant = "", Optional v As Variant = -1) As Variant
Static X As Long
Static n
Static s


    If v = -1 Then 'reset group on criteria
    
        X = 0
        n = 0
        s = ""
        
    ElseIf t < s Then 'reset group on refresh (selecting all to copy/paste for example)

        X = 1
        s = t
        n = v
   
    'elseif something - if user clicks on individual records, group value can change for example user clicks on 3rd record then the 2nd record

    ElseIf t > s Or (t = s And v > n + 1) Then 'increment by one
    
        X = X + 1
        s = t
        n = v
        
    End If
  
    CTGroup = X
    
End Function

edit: note the 'elseif something' comment. You have not said what you are going to do with the data, whether it needs to be editable, etc so you may need additional code to account for that situation.



Your query would be something like

SELECT Table1.ct1, Table1.ct2, ctgroup([ct1],[ct2]) AS ct3
FROM Table1
WHERE (((ctgroup())=False))
ORDER BY Table1.ct1, Table1.ct2;

which produces this result

[vishv.kemonte]

Please, could you send a file? Maybe I do smth wrong, but I have another result.

[CJ_London]

what result are you getting?

All you need to do is copy the function to a module and change the table name in your query to whatever your table is called.

If your example data is not representative of what you actually have, that is your problem

this is the table I created

ct1	ct2
KOH1	1
KOH1	2
KOH1	5
KOH1	6
KOH1	9
KOH2	1
KOH2	2
KOH2	5
KOH2	6
KOH2	9

[vishv.kemonte]

ct1	ct2	ct3
KOH1	1	1
KOH1	2	2
KOH1	5	3
KOH1	6	4
KOH1	9	5
KOH2	1	6
KOH2	2	7
KOH2	5	8
KOH2	6	9
KOH2	9	10

I got this result.
Yeah, the table is just like you showed.

[CJ_London]

looks like your ct2 field is text (it is showing as left justifed- should be numeric

[vishv.kemonte]

Yeah, thanks a lot. Happy NY!!!!

[vishv.kemonte]

Could you explain how you function works?
For example, comment all lines. I would be very grateful to you.
Your function is very useful for me and I would like to understand how it works.
Thanks in advance.

[vishv.kemonte]

I found out how the function worked. Thanks