Count duplicates only once

[Lifeseeker]

Hi,

I am trying to loop through a table and count the duplicate records only once. Please see the attached file.

1) In the test table, I want to loop through all pt_accts.

2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.

In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.

Is this possible via VBA?

Many thanks

 

[pbaldy]

Is it possible? Sure. Is it really what you want to do? What is the purpose of this field? It wouldn't be hard to count how many distinct values there were in the acct field.

 

[Lifeseeker]

Is it possible? Sure. Is it really what you want to do? What is the purpose of this field? It wouldn't be hard to count how many distinct values there were in the acct field.

I need this field for filtering in Excel later on. I essentially will need to output this table into Excel and apply conditional formula for subsequent steps, but for now I need to fill the 1s and 0s in this column.

Thanks

 

[pbaldy]

I'd open a recordset on an SQL statement that sorted by acct/id. Set a string variable to "". Then in a loop of the recordset, test the variable against the current value. If it's different, set the count field to 1 and set the variable to the current value. If it's the same, set the count field to 0.

 

[Lifeseeker]

I'd open a recordset on an SQL statement that sorted by acct/id. Set a string variable to "". Then in a loop of the recordset, test the variable against the current value. If it's different, set the count field to 1 and set the variable to the current value. If it's the same, set the count field to 0.

Hi,

This is what I have, but I am having trouble completing the loop part. How do you compare only the records where the pt_acct is the same? The code is in the mod.

Thanks

 

[pbaldy]

That won't even compile. Here's the first part:

  Set db = CurrentDb
  CurrentRec = ""
  Set rs = db.OpenRecordset("SELECT * FROM test ORDER BY pt_acct, ID")

  Do While Not rs.EOF

 

[Lifeseeker]

That won't even compile. Here's the first part:

  Set db = CurrentDb
  CurrentRec = ""
  Set rs = db.OpenRecordset("SELECT * FROM test ORDER BY pt_acct, ID")

  Do While Not rs.EOF

Ok, I have more codes, but it seems to an infinite loop.....what am I missing here?

Public Function cohortFinalizer()

Dim rs As DAO.Recordset
Dim db As Database
Dim CurrentRec As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select test.pt_acct from test order by test.pt_acct")

    
Do While Not rs.EOF
CurrentRec = rs("pt_acct")
    
If nextPt_acct <> rs("pt_acct") Then
rs.Edit
rs("pt_acct") = 1
rs.Update
Else

rs.Edit
rs("pt_acct") = 0
rs.Update
End If

Loop

End Function

 

[Gasman]

PMFJI,

Moving to a new record?

 

[sxschech]

not tested

Public Function cohortFinalizer()  
Dim rs As DAO.Recordset 
Dim db As Database 
Dim CurrentRec As String  

Set db = CurrentDb 
Set rs = db.OpenRecordset("select test.pt_acct from test order by test.pt_acct")
 
[COLOR=Red]CurrentRec = rs("pt_acct")[/COLOR]

Do While Not rs.EOF 
''CurrentRec = rs("pt_acct")      

   If [COLOR=Red]rs("pt_acct") <> CurrentRec [/COLOR]Then    
     rs.Edit    
     rs("pt_acct") = 1    
     rs.Update 
   Else    
     rs.Edit    
     rs("pt_acct") = 0    
     rs.Update 
   End If 

   [COLOR=Red]CurrentRec = rs("pt_acct")
   rs.movenext[/COLOR]
Loop  

End Function

 

[MarkK]

You do the .edit and .update on every loop, right? So you can do . . .

   rs.Edit    
   If rs("pt_acct") <> CurrentRec Then    
      rs("pt_acct") = 1    
   Else    
      rs("pt_acct") = 0    
   End If 
   rs.Update

 

[pbaldy]

Boy, you gotta be fast around here.

 

[Lifeseeker]

I changed to the right column names. Almost there. The loop works and while it seems that it is putting in a "1" only on the last record for each of the same pt_accts, which is fine for my case, the first two records are skipped. I do not know why.

Public Function cohortFinalizer()

Dim rs As DAO.Recordset
Dim db As Database
Dim CurrentRec As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select test.* from test order by test.pt_acct")

   
CurrentRec = rs("pt_acct")

Do While Not rs.EOF
    
If rs("pt_acct") <> CurrentRec Then
rs.Edit
rs("countable") = 1
rs.Update
Else

rs.Edit
rs("countable") = 0
rs.Update
End If

CurrentRec = rs("pt_acct")
rs.MoveNext
Loop

End Function

/[code]

Thanks

 

[vbaInet]

Boy, you gotta be fast around here.

... yes, it's hard keeping up with you Paul

 

[Lifeseeker]

Hi,

This almost works. The first two records are both showing a 0, which based on the logic, it should be 0. However, the first record has to be a 1 regardless.

How do I force that?

Public Function cohortFinalizer()

Dim rs As DAO.Recordset
Dim db As Database
Dim CurrentRec As String

Set db = CurrentDb
Set rs = db.OpenRecordset("test")
   

CurrentRec = rs("pt_acct")


Do While Not rs.EOF
    
 rs.Edit
   If rs("pt_acct") <> CurrentRec Then
      rs("countable") = 1
   Else
      rs("countable") = 0
   End If
 rs.Update

CurrentRec = rs("pt_acct")
rs.MoveNext
Loop

End Function

Thank you

 

[pbaldy]

... yes, it's hard keeping up with you Paul

I'm not the one on a post-a-thon, posting practically as much as everybody else combined.

 

[MarkK]

Remove the first instance of this line . . .

CurrentRec = rs("pt_acct")

. . . and observe what impact that has evaluating this . . .

   If rs("pt_acct") <> CurrentRec Then

. . . for the first record.

 

[vbaInet]

@pbaldy, not at all

 

[Lifeseeker]

Remove the first instance of this line . . .

CurrentRec = rs("pt_acct")

. . . and observe what impact that has evaluating this . . .

   If rs("pt_acct") <> CurrentRec Then

. . . for the first record.

It worked. Thank you. Is my understanding correct?

If I don't specify

CurrentRec = rs("pt_acct")

, then Access will start evaluating the next line at the beginning of the recordset.

If I do specify, then it actually starts evaluating on the first record.....?

Sounds like I am still confused still ha.

 

[MarkK]

When you do this evaluation . . .

   If rs("pt_acct") <> CurrentRec Then

. . . you are checking if the value changed since the last record, but if prior to the first loop you do this assignment . . .

CurrentRec = rs("pt_acct")

. . . then in the first loop you are comparing the value to itself, and it will always be equal to itself.