Filter out lines that come before certain text and only display line with certain text and the lines following.

HOTWATT
HOTWATT used Ask the Experts™
on
I have a table that pulls notes from a software we use. Only problem is each entered line in the software comes out on a separate line in the table in access. I need to pull out only the disposition from the table. Not sure how to do that because the disposition could be on multiple lines. I need to pull anything that starts with Disposition and the subsequent lines following  that since disposition is always at the end and usually is on multiple lines. Everything before disposition needs to be filtered out. I attached two images that might help you understand what I am looking for a little better. Any help would be much appreciated!

SQL
SELECT OH.OH_NUMBER, PK1_NL.NL_LINE, PK1_NL.NL_LINE_NO
FROM OH INNER JOIN PK1_NL ON OH.OH_NMSEQNUM = PK1_NL.NL_NMSEQNUM;

Open in new window

Table-example.JPG
Outcome-I-am-looking-for-example.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
See if this works.

I only created a single table to test with.  Just replace my table with your join syntax.
SELECT t1.oh_number, t1.nl_line, t1.nl_line_no
FROM Table1 AS t1,
	(SELECT oh_number, nl_line, nl_line_no
	FROM Table1 where nl_line like 'Disposition*'
	)  AS t2
WHERE t1.oh_number=t2.oh_number AND t1.nl_line_no>=t2.nl_line_no
order by t1.nl_line_no;

Open in new window

lcohanDatabase Analyst

Commented:
You will need to select all lines where NL_LINE_NO > NL_LINE_NO  WHERE  NL_LINE LIKE 'Disposition%' for all entries (GROUP BY)  of the same OH_NUMBER. I think that should be something like code below where you need to replace "oh_table" with the actual table name where the data is stored :
select oh_number, nl_line, nl_line_no 
from oh_table o with (nolock) 
	inner join (select oh_number, nl_line_no from OH_table with (nolock) where nl_line like 'Disposition%') q on o.oh_number = q.oh_number
where  o.nl_line_no >= q.nl_line_no;

Open in new window

Author

Commented:
Thanks slightwv that worked perfectly!!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial