Concatenate In Order By Another Field

HOTWATT
HOTWATT used Ask the Experts™
on
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
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:
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

Author

Commented:
How do you go about Concatenateing NL_LINES together into one line in order of NL_LINE_NO?
Most Valuable Expert 2012
Distinguished Expert 2018

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

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);
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.
Retired IT Professional
Commented:
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
Hamed NasrRetired IT Professional

Commented:
Edited previous comment to replace modified concat.accdb.
It includes table DISPO (OH_NUMBER, NL_LINE, NL_LINE_NO) and related queries.

Author

Commented:
Thanks hnasr that was extremely helpful and exactly what I was looking for!!
Hamed NasrRetired IT Professional

Commented:
Welcome!

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