MS Access query to return a column based on another column if it is consecutive numbers

Shake Ers
Shake Ers used Ask the Experts™
on
I have a table in Access with 3 columns: first is transactionID, the second is textline, 3rd is msg.  check example below pls            
            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564231      9      hi
564202      1      hello
564202      2      hi
564202      8      hello
564202      22      hi
513830      7      hello
513830      10      hi
513830      13      hello
            
I would like the result to be as follows            
Return the transactionID, textline, msg for  those with consecutive textlines per transactionID            
            
transactionID      textline      msg
564231      3      hello
564231      4      hi
564231      5      hello
564202      1      hello
564202      2      hi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Try something like:

SELECT T.* FROM yourTable as T
WHERE exists (
SELECT TransactionID
FROM yourTable
WHERE TransactionID = T.TransactionID
AND (ABS(TextLine-T.TextLine) = 1))
ORDER BY T.TransactionID, T.TextLine
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can run this query:

SELECT 
    Table.transactionID, Table.textline, Table.msg
FROM 
    [Table], 
    (SELECT  T.transactionID, T.textline
    FROM [Table] AS T)  AS T1
WHERE 
    (((Table.transactionID)=[T].[transactionID]) 
    AND 
    ((Abs([Table].[textline]-[T1].[textline]))=1))
ORDER BY
    Table.transactionID DESC , 
    Table.textline;

Open in new window

11.PNG

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