Getting rid of Duplicates in a Field

[CankleSteve]

Hi I'm new to VBA and Access and was wondering if anyone knew of a way to go through my field to replace the duplicated string separated by a new line and carriage return:

Field Ex:
A
A
A
B
B
C
C
C
C

With:
A
B
C

I really appreciate any help.

[twinnyfo]

CankleSteve,

Welcome to Bytes!

I'm not sure I understand your question. Is your first list of characters the contents of one field in one record or is this a series of records from one field in a table?

The answer to this will certainly determine how we approach this solution.

Standing by to hepp!

Thanks!

[CankleSteve]

Twinnyfo,

They are a list of one field in one record. So the record's field would read like the long above list. I would like help figuring out how I can "scan" through them and take out any duplicates.

Note: they seem to all be in alphabetical order, so in no cases will it go like A /n/r A /n/r B /n/r A for example.

[twinnyfo]

So, I will "describe" the solution, without coding it out, but we can always work toward a final solution if needed.

I would create a user-defined function that takes the value of the field ans assigns that value to an array, using the Split() function, with the separating value of Chr(13)--which is the carriage return. Once it is in an array, assign the first character to a string variable. Cycle through the values in the array and when a value other than the current value is found, add a carriage return and that character to your string variable. After going through the entire array, return the value of that string.

Again, this is a description, but at the moment I am unable to write out code for this, but I'm glad to continue efforts on this.

If you have no idea what I am talking about, then we will have to go through things step by step. That's OK - everyone has to begin learning VBA at some point.

Hope this hepps!

[ADezii]

Although not an easy task, I'll try to step into twinnyfo's shoes for a minute, and hopefully I'll be on the right track.

1. I have no access to Access (no pun intended) right now, so I will simulate a Field Value in Excel, then pass it to a Function to be analyzed.
   

   Dim strTest As String
    
   'Simulate Field Value
   strTest = "A" & vbCrLf & "A" & vbCrLf & "A" & vbCrLf & "B" & vbCrLf & _
             "B" & vbCrLf & "B" & vbCrLf & "C" & vbCrLf & "C" & vbCrLf & _
             "C" & vbCrLf & "C"
    
   'Debug.Print strTest         'Make sure I got it right!
    
   Debug.Print fAnalyzeString(strTest)

2. As twinnyfo has suggested, I'll split the String into an Array consisting of individual Elements with a CrLf being the Delimiter.
3. I will use a variable and extract the 1st character from the Array.
4. Now, check this character against every element in the Array, and if there is no match, start building the Variable String. You are actually checking the rightmost character of the Build String against elements of the Array.
5. Repeat this process until every character in the Build String has been checked against every element in the Array.
6. Function Definition:
   

   Public Function fAnalyzeString(strString As String) As String
   Dim varString As Variant
   Dim strChars As String
   Dim intCtr1 As Integer
   Dim intCtr2 As Integer
    
   varString = Split(strString, vbCrLf)
    
   strChars = varString(0)
    
   For intCtr1 = 1 To Len(strChars)
     For intCtr2 = LBound(varString) To UBound(varString)
       If Right$(strChars, 1) <> varString(intCtr2) Then
         strChars = strChars & vbCrLf & varString(intCtr2)
       End If
     Next
   Next
    
   fAnalyzeString = strChars
   End Function

7. OUTPUT:
   

   A
   B
   C

P.S. - Looking at my explanation, I do realize that it may not be the best in the world. Perhaps twinnyfo can make it much clearer.

[isladogs]

There are many ways of dealing with this situation. For example:

Create a new empty table with the same fields but index your field (the one with dupes) and set as no dupes allowed.

Now create an append query to copy all data into the new table.
Because you have set a unique index, only one record will be appended for each duplicate

Hope that helps

[twinnyfo]

@ADezii
I have not checked your math, but your approach is spot on to the direction I was going. It's a great outline to start with.

@isladogs
If you read the rest of this thread carefully, you will notice that your solution does not address the issue. Note, that this was my first question to OP, and we determined that the field itself has these duplicate values, not a series of fields.

@CankleSteve
I hope this has given you something to work with, or at least begin work on a solution for your table.

Standing by with more hepp if you need it....

[isladogs]

@twinnyfo
I've re-read the thread carefully but your response wasn't clear!
I think you meant a single record in a field has these duplicate values not a series of records. If that is the case, my suggestion won't work.

[twinnyfo]

Isladogs,

Fields don’t have records, records have fields. Your solution is based upon one field among multiple records. The question is about one field in one record that has a “stacked” value with duplicate values in that “stack”.

As a side note, although your method may have worked under a different scenario, it is usually not wise to force the loss of a record via index violations. This would require adequate and accurate error handling to keep the DB from halting, not to mention the loss of all the data in the other fields of each record rejected.

Hope this makes sense and hope it hepps you understand this issue a bit better.

[isladogs]

Twinnyfo
Yes, my answer was also poorly worded regarding fields and records.
Thank you but I already understood the issue without your additional explanation
I'm also fully aware of potential side effects of deleting any data.
Not sure why you keep saying 'hepp' when you mean help.

[NeoPa]

"Not sure why you keep saying 'hepp' when you mean help."
Not sure how old you are C but if you remember back a few years (OK. Decades. I mean decades alright :-( ) when The Beverly Hillbillies was showing on terrestrial TV over here then imagine TwinnyFo (24 in same accent) speaking in a similar dialect.

He tells me it isn't from that show but another I (probably we) are less familiar with but it's about stock car racing in that same region I believe.

Anyway, all I can really say is that Twinny, as you'll see from his post count, is one of our most prolific and helpful expert mods.

All the best - and I hope life out West is treating you well. You can imagine how much fun it is to be close to Town right now but we're managing.

[isladogs]

Hi NeoPa
I'd forgotten all about my slightly irritated responses in this thread. Anyway, thanks for the explanation as well as the derivation of twinnyfo's name.

I believe I'm quite a few years older than you but I have to confess that I never saw the Beverley Hillbillies back in the day.

I do count myself lucky to be living in the middle of nowhere (Exmoor). Even without COVID, I'd find it very hard to live in a town again after so many years away. I'd noticed you hadn't been around for a while. Good luck and stay safe

[NeoPa]

IslaDogs:
I believe I'm quite a few years older than you but I have to confess that I never saw the Beverley Hillbillies back in the day.

I wouldn't have known, there again I maybe haven't actually seen you much. When we spoke it was just on the microphone mainly.

I just checked when it stopped showing. It was March 1971. As a point of reference that was about a month after we completed conversion of the currency to decimal!

[twinnyfo]

Henry,

I believe Post #5 has an adequate outline/solution. OP has not responded to let us know if this approach is acceptable in his situation.

[NeoPa]

Both posts #4 & #5 describe solutions that appear adequate to me. Are they for you?

If so then you have your answer. If not, please feel free to post a question explaining what you need to know. Your question would have to be in its own separate thread of course.