Pls help putting this in access query

[Kiron]

Need help with applying an Excel formula in my access tool.

Excel formula is
=If(a1<>a2,1,b+1)

Basically what it does is it checks the current value with previous value and returns a 1 if duplicate and if not duplicate it adds the value by 1. If any change in the sequence is found, the counter resets to 0 and starts counting for that new value.
I need this to work in access but dont know how.
Basically am trying to locate the 20th untouched record from my database for audit. So i am bucketing them in 20s.

My current access formula in query does
Dcount("id","table","id<=" & id & "consol='" & consol &"'") mod 20 which works fine for a counter but doesn't reset counter to 0 when change in record is found.

Problem
Consolidate counter
Kiraccden 1
Kiraccden 2
Kiraccden1 1
Kiraccden 3
Kiraccden 4

Required solution
Consolidate counter
Kiraccden 1
Kiraccden 2
Kiraccden1 1
Kiraccden 1
Kiraccden 2

In above example i expect the case 3 to reset my counter to 1. Instead it continues from case 2.

Please advise on a formula i can put to my query to do this please.

Thanks heaps champs.

 

[arnelgp]

always add autonumber to your table, this is very helpful.
when you already have the autonumber field, create this function in a module:

public function fnCounter(id as long, tbl as string) as long
dim sConsolidate as string
dim i as long
with currentdb.openrecordset("select * from " & tbl & _
" order by yourAutoNumberFieldName;")
    if not (.bof and .eof) then 
        .findfirst "autoNumberFieldName = " & id
        sConsolidate = !Consolidate
        do while not .bof
            if sConsolidate<> !Consolidate then exit do
            i = i + 1
            .moveprevious
        loop
    end if
end with
fnCounter = i
End function

now build your query:

select id, consolidate, fnCounter([id], "yourTableNameHere") as counter from yourTableNameHere;"

 

[Kiron]

Hi arnelgp
Your solution worked. Am able to get the desired result.
Thanks heaps. U r a true champion.
Really Appreciate your help