?
Solved

MS Access query to parse string into 2 records

Posted on 2014-01-06
5
Medium Priority
?
427 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
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 38

Accepted Solution

by:
PatHartman earned 2000 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
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

771 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