Need help with applying an Excel formula in my access tool.
Excel formula is
=Mod(If(a1<>a2,1,b1+1),20)
Basically what it does is it checks the current value with previous value and returns a 1 if duplicate and starts a counter till 20 if all duplicates. Repeats with duplicate values. If any change in Value / new value 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.
I have a formula in query called
Dcount(record,table,id<=id) mod 20 which works fine for a counter but doesn't reset counter to 0 when change in record is found.
Problem
User case skill audited 20thcheck
A 1234 acc 1
A 2345 acc 2
A 3245 fin 1
A 3456 acc 3
A 6754 acc 4
Expected solution
User case skill audited 20thcheck
A 1234 acc 1
A 2345 acc 2
A 3245 fin 1
A 3456 acc 1
A 6754 acc 2
In above example i expect the case 4 to reset my counter (20th check) 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.
Excel formula is
=Mod(If(a1<>a2,1,b1+1),20)
Basically what it does is it checks the current value with previous value and returns a 1 if duplicate and starts a counter till 20 if all duplicates. Repeats with duplicate values. If any change in Value / new value 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.
I have a formula in query called
Dcount(record,table,id<=id) mod 20 which works fine for a counter but doesn't reset counter to 0 when change in record is found.
Problem
User case skill audited 20thcheck
A 1234 acc 1
A 2345 acc 2
A 3245 fin 1
A 3456 acc 3
A 6754 acc 4
Expected solution
User case skill audited 20thcheck
A 1234 acc 1
A 2345 acc 2
A 3245 fin 1
A 3456 acc 1
A 6754 acc 2
In above example i expect the case 4 to reset my counter (20th check) 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.