Concatenate In Order By Another Field

I have a table called DISPO. I am trying to concatenate the field called NL_LINE. Issue is when I do that it concatenates in alphabetical order so the sentence does not display correctly. Can I use the field NL_LINE_NO to order it correctly when I concatenate NL_LINE? I attached examples so you can understand what I am asking a little better. If anyone could give me a hand that would be much appreciated!
Examples.JPG
HOTWATTAsked:
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:
Just change the order by from the other question:
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;

Open in new window

0
HOTWATTAuthor Commented:
How do you go about Concatenateing NL_LINES together into one line in order of NL_LINE_NO?
0
slightwv (䄆 Netminder) Commented:
I'm not an Access Expert.  I thought you already had concatenation working and just the order was wrong.

Since you don't know how many rows will be returned, it appears you need to write your own module to do this.

There are several examples out there:
http://allenbrowne.com/func-concat.html

I cannot recommend one over the other.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

slightwv (䄆 Netminder) Commented:
I also wanted to throw out there that you might consider UCASE or LCASE for the order by:
...
order by UCASE(t1.nl_line);
0
Gustav BrockCIOCommented:
First, create a simple query where you define the sort order:

PARAMETERS [Key] Long;
SELECT *
FROM DISPO
WHERE OH_NUMBER=[Key]
ORDER BY NL_LINE_NO

Open in new window

Save this as qdySource.

Then create the final query to concatenate the records:

SELECT OH_NUMBER, ConcatenateRecords("qdySource",[OH_NUMBER],"[NL_LINE]"," ") AS NL_LINES
FROM DISPO
GROUP BY OH_NUMBER;

Open in new window

using this function:

Public Function ConcatenateRecords( _
  ByVal strSource As String, _
  ByVal lngKey As Long, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter Value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  
  On Error GoTo Err_ConcatenateRecords
  
  Set dbs = CurrentDb()
  
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = lngKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.Value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
    
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  
  Set dbs = Nothing
  
  ConcatenateRecords = strFields
  
Exit_ConcatenateRecords:
  Exit Function
  
Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords
  
End Function

Open in new window

The function you can just copy-n-paste into a new module, then compile and save.
0
hnasrCommented:
Modify this database as per your liking.
table b(aid, bdesc, bid)
aID	bID	bdesc
1	1	1-one
1	2	1-two
1	3	1-three
2	1	2-one
2	2	2-two
2	3	2-three

Open in new window

query a_concat to group row titles
SELECT aid
FROM b
GROUP BY aid;

Open in new window

aid
1
2

Open in new window

Function to concatenate bdesc
Public Function b_concat(i As Variant)
    Dim b_rs As Recordset
    Set b_rs = CurrentDb.OpenRecordset("select bdesc from b Where aid=" & "'" & i & "'")
    Dim s As String
    s = "Disposition: "
    Do While Not b_rs.EOF
        s = s & b_rs("bdesc") & " "
        b_rs.MoveNext
    Loop
    b_concat = s
End Function

Open in new window


Output query b_concat
SELECT a_concat.aid, b_concat([aid]) AS Disposition
FROM a_concat;

Open in new window

aid	Disposition
1	Disposition: 1-one 1-two 1-three 
2	Disposition: 2-one 2-two 2-three 

Open in new window

concat-1.accdb
0

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
hnasrCommented:
Edited previous comment to replace modified concat.accdb.
It includes table DISPO (OH_NUMBER, NL_LINE, NL_LINE_NO) and related queries.
0
HOTWATTAuthor Commented:
Thanks hnasr that was extremely helpful and exactly what I was looking for!!
0
hnasrCommented:
Welcome!
0
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
Query Syntax

From novice to tech pro — start learning today.