?
Solved

VBA / SQL - Find and replace (MS Access)

Posted on 2016-09-29
4
Medium Priority
?
83 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 2000 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 22
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 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