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

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!


Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
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

HOTWATTAuthor Commented:
Thanks slightwv that worked perfectly!!
slightwv (䄆 Netminder) Commented:
Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.