在資料表的操作上，應該都遇到需要由Head與Body兩個表合併顯示內容的機會，例如Head記錄為財產類型，Body為財產子類型，將兩個表合併顯示時，會是這樣的內容：

但如果我想這樣呈現，要怎麼做到？

筆者建立了一個Function 陳述式，可以從查詢語句中，插入CST語句來進行產生清單的動作，用來達到這樣的顯示結果，以節省開發成本。

Function CST( _
         strField, _
         strTable, _
         strWhere, _
         Optional strGroup = "", _
         Optional strOrderBy = "", _
         Optional strInterval = " ", _
         Optional strNewLine = vbCrLf, _
         Optional bnNumbering As Boolean = False, _
         Optional strBullets As String = "" _
         ) As String
         
'ConcatenateStringsTogether
'將某資料表依照strWhere,strGroup等帶出Data Records後
'依序將指定的strField資料合併成單一資料
'然後再傳回給SQL使用
'strField    要顯示的欄位
'strTable   資料表名稱
'strWhere   Where語句
'strGroup   Group語句
'strOrderBy OrderBy語句
'strInterval 各欄位間隔填充字串
'strNewLine  換行使用字元
'bnNumbering 是否編號
'strBullets  顯示項目符號字元

    strSQL = "SELECT " & strField & " FROM " & strTable
    
    If strGroup <> "" Then
        strSQL = strSQL & vbCrLf & " GROUP BY " & strGroup
        
    End If
    
    If strWhere <> "" And strGroup = "" Then
        strSQL = strSQL & vbCrLf & " WHERE " & strWhere
        
    ElseIf strWhere <> "" And strGroup <> "" Then
        strSQL = strSQL & vbCrLf & " HAVING " & strWhere
        
    End If
    
    If strOrderBy <> "" Then
        strSQL = strSQL & vbCrLf & " ORDER BY " & strOrderBy
        
    End If
    
    On Error Resume Next
    
    strData = ""
    Set m = CurrentDb.OpenRecordset(strSQL)
    j = 1
    If m.EOF = False Then
        Do
            If strData <> "" Then strData = strData & strNewLine
            strData2 = ""
            For i = 0 To m.Fields.Count - 1
                If strData2 <> "" Then strData2 = strData2 & strInterval
                strData2 = strData2 & Trim(m(m.Fields(i).Name))
            Next
            If bnNumbering Then
                strData = strData & Format(j, "0. ")
            End If
            If strBullets <> "" Then
                strData = strData & strBullets
            End If
            strData = strData & strData2
            m.MoveNext
            j = j + 1
        Loop Until m.EOF
    End If
    
    CST = strData
    
End Function

原本的SQL語句：

SELECT CT.NAME_ENG AS 資產類型, CST.NAME_ENG AS 資產子類型
FROM CategoryType AS CT INNER JOIN CategorySubType AS CST ON CT.INDEX = CST.CategoryType_INDEX
WHERE (((CT.NAME_ENG)="Printer" Or (CT.NAME_ENG)="LCD"))
ORDER BY CT.NAME_ENG, CST.NAME_ENG;

改成CST後的語句：

SELECT CT.NAME_ENG AS 資產類型, CST("NAME_ENG","CategorySubType","CategoryType_INDEX=" & [CT].[INDEX],"","NAME_ENG"," ",Chr(13) & Chr(10),True,"-> ") AS 資產子類型
FROM CategoryType AS CT
WHERE (((CT.NAME_ENG)="Printer" Or (CT.NAME_ENG)="LCD"))
ORDER BY CT.NAME_ENG;

程式中，「bnNumbering/是否編號」與 「strBullets/顯示項目符號字元」可以同時使用，也可以單獨使用，可以搭配出不同的效果。