Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
711 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 1500 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

607 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