Solved

VBA / SQL - Find and replace (MS Access)

Posted on 2016-09-29
4
72 Views
Last Modified: 2016-09-29
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
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 41821800
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
 

Author Closing Comment

by:Seamus2626
ID: 41821918
Perfect, thanks Barry!
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 41821948
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
 
LVL 21
ID: 41822481
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question