Solved

MS Access query to parse string into 2 records

Posted on 2014-01-06
5
426 Views
Last Modified: 2014-01-06
I have a text field called "Title" with records like this:

Selexipag for pulmonary arterial hypertension
Rilotumumab (AMG-102) for Gastric tumors with high MET expression
Vicrostatin for Cancer


I would like to parse these records in 2 fields: Drug and Indication, so that the query return two columns:

Drug                                   Indication
Selexipag                            pulmonary arterial hypertension
Rilotumumab (AMG-102)   Gastric tumors with high MET expression
Vicrostatin                          Cancer
0
Comment
Question by:tomfolinsbee
[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
  • 2
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759377
if the term "for" will separate the drug from the indication, then you could use something like:

SELECT Left([yourFieldname], instr([yourFieldName], "for") -1) as Drug,
             Mid([yourFieldname], instr([yourFieldName], "for") + 4) as Indication
FROM yourtable
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39759390
Lecture first - fields should be atomic.  They should never contain multiple attributes.

If the field is reliably constructed so that the drug and indication are ALWAYS separated by the word " for " surrounded by spaces, you can use that string as the delimiter.

SELECT Table1.Title, Left([Title],InStr([Title]," for ")-1) AS Drug, Mid([Title],InStr([texTitlet1]," for ")+5) AS indication
FROM Table1;

If there is any deviation in the formatting of the string, all bets are off.
0
 

Author Closing Comment

by:tomfolinsbee
ID: 39759413
Worked perfectly first time. Thank you!
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759433
Guess I should have used [Title] instead of [YourFieldName]!
0
 

Author Comment

by:tomfolinsbee
ID: 39759441
Dale, very sorry. I didn't see your post, I should have scrolled up. Small screen. Again apologies for not splitting the points. My mistake...
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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 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