Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA / SQL - Find and replace (MS Access)

Posted on 2016-09-29
4
Medium Priority
?
93 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 39

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

610 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