• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

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

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
darls15
Asked:
darls15
  • 2
  • 2
1 Solution
 
COACHMAN99Commented:
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
 
darls15Author Commented:
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
 
COACHMAN99Commented:
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
 
darls15Author Commented:
Thanks for your assistance COACHMAN99. Solution wasn't quite what I was after but is one I will go with.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now