Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

VBA / SQL - Find and replace (MS Access)

Hi, i have an access table where i have multiple organisations in one field

eg

abc, xyz, oec, mba etc

In many fields i have a problem of a repeated organisation e.g

abc, abc, abc, oec, xyz, xyz
abc, abc
oec, abc, abc

Is there a line of sql (or VBA) i can run into my table to remove the duplicates from each line

Many thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
1 Solution
 
Barry CunneyCommented:
In MS Access please put the following function in a module

Function RemoveDuplicates$(FieldString$)

Dim aryInitial As Variant
Dim strFinal As String
Dim i As Long

aryInitial = Split(FieldString$, ",")

For i = LBound(aryInitial) To UBound(aryInitial)

If InStr(strFinal, Trim(aryInitial(i))) = 0 Then
strFinal = strFinal & aryInitial(i) & ","
End If

Next i

RemoveDuplicates$ = strFinal

End Sub

Open in new window



In a query you can then refer to this function in an expression field
RemoveDuplicates$([FieldWithDups])

and this query can then be an insert or update to get the de-duplicated values to whichever table/field you wish
0
 
Seamus2626Author Commented:
Perfect, thanks Barry!
0
 
PatHartmanCommented:
The best solution would be to normalize the tables.  Violating first normal form always leads to problems.  You should create a separate table to hold the many-side items.  You can also create a third table to control the actual values to ensure that people don't add items accidentally due to typos.

tblOrganization
OrgID (autonumber primary key)
OrgName
OrgCD

tblPersonOrganization (replace "person" with what ever your entity is)
RecID (autonumber primary key)
OrgID (foreign key to tblOrganization)
PersonID (foreign key to tblPerson - or whatever your entity is)
JoinDate

Then on the form, use a subform to enter organizations.  If you need help building this structure, I can post a many-to-many example to get you started.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
like Pat, I believe that creating a table to store the related organization values is best -- then you can do much more efficient reporting too
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now