Solved

VBA / SQL - Find and replace (MS Access)

Posted on 2016-09-29
4
35 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
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 34

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 18
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now