Solved

Replace a part string with another part string in a field using a table as the criteria lookup

Posted on 2014-03-04
4
674 Views
Last Modified: 2014-03-05
I have a main table which contains thousands of records - tblPrograms. As there are many staff continually adding records to the table I end up with all sorts of variations for part strings of titles in the ProgramTitle field.

For example, the part string of "OneChannel" is sometimes input as "One Channel", "One_Channel" or "One-Channel" which makes querying the data difficult.

So, to try and assist in cleansing the data for reporting, I have created a table (TitleAdjustments) which has 2 columns - "PartStringEntered" and "PartStringReplaced".  I want to add to this table as I find these types of inconsistences.

What I am hoping for is a function that basically finds the "PartStringEntered" in ProgramTitle field of tblPrograms table and only replaces that part of the title with "PartStringReplaced".

I do not want to permanently change the title in tblPrograms, only use this function in querying the data.

Is this possible?

Thanks in advance
darls15
0
Comment
Question by:darls15
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39905220
Why don't you have a program lookup table, and only store the program id in the main table?

If your query joins on the two part-strings and only shows the required one this will work (although joining large tables on strings isn't a good idea)
0
 

Author Comment

by:darls15
ID: 39905281
Hi COACHMAN99

The data is a direct export from a web-based system which is accessed by staff in many locations around the state. Program titles have been and continue to be entered inconsistently. One rule is that the titles must contain the "OneChannel" identifier (and others, this is just an example) for querying purposes once exported to my database. However these identifiers have been and continue to be entered in many variations and I have no control over this.

As a fresh export of the data is done monthly and I really need a method of "cleansing" these identifiers to produce updated reports.

"Support Staff" is another identifier and gets entered as "SupportStaff", "Support_Staff" and "Support-Staff".

Any other suggestions?

Thanks
darls15
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
ID: 39905388
other than a new list of all 'mis-spelled' choices in one column, and the preferred spelling in another as you have, probably not.
0
 

Author Closing Comment

by:darls15
ID: 39908006
Thanks for your assistance COACHMAN99. Solution wasn't quite what I was after but is one I will go with.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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