Need help converting excel formula to access (1 Viewer)

Kiron · Jul 23, 2019

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.

theDBguy · Jul 23, 2019

Hi. The difference between Excel and Access is Excel can process the information in a specific order. Whereas, Access can only do it if you can specify the process order. So, is there something in your table you can use to specify the order of the data?

arnelgp · Jul 23, 2019

what is the basis of "changing in record"? by User & skill?
create this function in a Standard Module (VBA->Insert->Module).

Code:

Public Function fn20Check(pUser As Variant, pCase As Variant, pSkill As Variant) As Integer
	Dim rs As DAO.Recordset
	Dim db As DAO.Database
	Dim iCount As Integer

	Set db = Currentdb
	Set rs = db.OpenRecordset("yourTableNameHere", dbOpenSnapshot)

	With rs
		.FindFirst "[User] = " & Chr(34) & pUser & Chr(34) & " And " & _
			"[Case] = " & Chr(34) & pCase & Chr(34) & " And " & _
			"[Skill] = " & Chr(34) & pSkill & Chr(34)

		DO While Not .BOF
			If ![User] <> pUser Or ![Skill] <> pSkill Then
				Exit Do
			End If 
			iCount = iCount + 1
			.MovePrevious
		Loop
		.Close
	End With
	Set rs = Nothing
	Set db = Nothing
	
	fn20Check = iCount
End Function

now create your query, calling the function from the query:

Code:

Select [User], [Case], [Skill], fn20Check([User], [Case], [Skill]) As 20thCheck From yourTableNameHere;

Need help converting excel formula to access (1 Viewer)

Kiron

New member

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread