Solved

MS Access query to parse string into 2 records

Posted on 2014-01-06
5
422 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
  • 2
  • 2
5 Comments
 
LVL 47

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 35

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 47

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 32
Help Required 3 97
Query Help - MSSQL - Averages 5 27
Label with definitive value after closing ms access 19 14
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 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