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
666 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

803 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