[SQL] MS Access에서 한 줄에 거기에 여러 값을 가진 여러 행을, 합치

MS Access에서 한 줄에 거기에 여러 값을 가진 여러 행을, 합치

나는 간단한 요구 사항 관리 데이터베이스를 만들려고하고 있습니다. 기본적으로 나는 다음과 같은 두 테이블이 :

2 열 Contract_requirements :

CR_ReqID    |   Description
reqCR1      |   Contract req description 1
reqCR2      |   Contract req description 2

SW_requirements

Title               |   SW_ReqID     |  RootReq
SW req description 1|   reqSW1       |   reqCR1, reqCR2
SW req description 2|   reqSW2       |   reqCR1
SW req description 3|   reqSW3       |   reqCR2

그리고 나는 그런 테이블을받을 쓰기 쿼리 싶습니다 :

CR_ReqID  |Description                  |where used?
reqCR1    |Contract req description 1   |reqSW1, reqSW2  
reqCR2    |Contract req description 2   |reqSW1, reqSW3

테이블 "계약 요구 사항"과 "SW 요구 사항은"열 "RootReq"를 통해 관계에

앨런 브라운에서 코드를 구현하려 필자는 http://allenbrowne.com/func-concat.html#Top

이것은 내 쿼리입니다

SELECT Contract_requirements.CR_ReqID, ConcatRelated("SW_ReqID     ","SW_requirements","RootReq = """ & [CR_ReqID] & """") AS Expr1
FROM Contract_requirements;

하지만 난 Access에서 오류가

너희들은 내가이 작업을하는 데 도움이 수 있을까요? 미리 감사드립니다

해결법

1. ==============================

   1.개별 레코드에 다중 값 필드 요소를 확장하는 쿼리를 구축 할 수 있습니다.

   개별 레코드에 다중 값 필드 요소를 확장하는 쿼리를 구축 할 수 있습니다.

   쿼리 1

   SELECT SW_Requirements.Title, SW_Requirements.SW_ReqID, SW_Requirements.RootReq.Value 
   FROM SW_Requirements;
   

   그런 다음 ConcatRelated () 함수에 대한 소스로 해당 쿼리를 사용합니다.

   SELECT Contract_Requirements.*, 
   ConcatRelated("SW_ReqID","Query1","[SW_Requirements.RootReq.Value]='" & [CR_ReqID] & "'") AS WhereUsed
   FROM Contract_Requirements;
   

   조언 명명 규칙에 공백이나 문장 부호 / 특수 문자를 사용하지.

2. ==============================

   2.이 소스로 따라서 당신이 필요로하지 않습니다 추가 저장 한 쿼리를 SQL을 받아 들일 것 같이 또한 내 DJoin 기능을 사용할 수 있습니다 :

   이 소스로 따라서 당신이 필요로하지 않습니다 추가 저장 한 쿼리를 SQL을 받아 들일 것 같이 또한 내 DJoin 기능을 사용할 수 있습니다 :

   ' Returns the joined (concatenated) values from a field of records having the same key.
   ' The joined values are stored in a collection which speeds up browsing a query or form
   ' as all joined values will be retrieved once only from the table or query.
   ' Null values and zero-length strings are ignored.
   '
   ' If no values are found, Null is returned.
   '
   ' The default separator of the joined values is a space.
   ' Optionally, any other separator can be specified.
   '
   ' Syntax is held close to that of the native domain functions, DLookup, DCount, etc.
   '
   ' Typical usage in a select query using a table (or query) as source:
   '
   '   Select
   '       KeyField,
   '       DJoin("[ValueField]", "[Table]", "[KeyField] = " & [KeyField] & "") As Values
   '   From
   '       Table
   '   Group By
   '       KeyField
   '
   ' The source can also be an SQL Select string:
   '
   '   Select
   '       KeyField,
   '       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
   '   From
   '       Table
   '   Group By
   '       KeyField
   '
   ' To clear the collection (cache), call DJoin with no arguments:
   '
   '   DJoin
   '
   ' Requires:
   '   CollectValues
   '
   ' 2019-06-24, Cactus Data ApS, Gustav Brock
   '
   Public Function DJoin( _
       Optional ByVal Expression As String, _
       Optional ByVal Domain As String, _
       Optional ByVal Criteria As String, _
       Optional ByVal Delimiter As String = " ") _
       As Variant
   
       ' Expected error codes to accept.
       Const CannotAddKey      As Long = 457
       Const CannotReadKey     As Long = 5
       ' SQL.
       Const SqlMask           As String = "Select {0} From {1} {2}"
       Const SqlLead           As String = "Select "
       Const SubMask           As String = "({0}) As T"
       Const FilterMask        As String = "Where {0}"
   
       Static Values   As New Collection
   
       Dim Records     As DAO.Recordset
       Dim Sql         As String
       Dim SqlSub      As String
       Dim Filter      As String
       Dim Result      As Variant
   
       On Error GoTo Err_DJoin
   
       If Expression = "" Then
           ' Erase the collection of keys.
           Set Values = Nothing
           Result = Null
       Else
           ' Get the values.
           ' This will fail if the current criteria hasn't been added
           ' leaving Result empty.
           Result = Values.Item(Criteria)
           '
           If IsEmpty(Result) Then
               ' The current criteria hasn't been added to the collection.
               ' Build SQL to lookup values.
               If InStr(1, LTrim(Domain), SqlLead, vbTextCompare) = 1 Then
                   ' Domain is an SQL expression.
                   SqlSub = Replace(SubMask, "{0}", Domain)
               Else
                   ' Domain is a table or query name.
                   SqlSub = Domain
               End If
               If Trim(Criteria) <> "" Then
                   ' Build Where clause.
                   Filter = Replace(FilterMask, "{0}", Criteria)
               End If
               ' Build final SQL.
               Sql = Replace(Replace(Replace(SqlMask, "{0}", Expression), "{1}", SqlSub), "{2}", Filter)
   
               ' Look up the values to join.
               Set Records = CurrentDb.OpenRecordset(Sql, dbOpenSnapshot)
               CollectValues Records, Delimiter, Result
               ' Add the key and its joined values to the collection.
               Values.Add Result, Criteria
           End If
       End If
   
       ' Return the joined values (or Null if none was found).
       DJoin = Result
   
   Exit_DJoin:
       Exit Function
   
   Err_DJoin:
       Select Case Err
           Case CannotAddKey
               ' Key is present, thus cannot be added again.
               Resume Next
           Case CannotReadKey
               ' Key is not present, thus cannot be read.
               Resume Next
           Case Else
               ' Some other error. Ignore.
               Resume Exit_DJoin
       End Select
   
   End Function
   
   ' To be called from DJoin.
   '
   ' Joins the content of the first field of a recordset to one string
   ' with a space as delimiter or an optional delimiter, returned by
   ' reference in parameter Result.
   '
   ' 2019-06-11, Cactus Data ApS, Gustav Brock
   '
   Private Sub CollectValues( _
       ByRef Records As DAO.Recordset, _
       ByVal Delimiter As String, _
       ByRef Result As Variant)
   
       Dim SubRecords  As DAO.Recordset
   
       Dim Value       As Variant
   
       If Records.RecordCount > 0 Then
           While Not Records.EOF
               Value = Records.Fields(0).Value
               If Records.Fields(0).IsComplex Then
                   ' Multi-value field (or attachment field).
                   Set SubRecords = Records.Fields(0).Value
                   CollectValues SubRecords, Delimiter, Result
               ElseIf Nz(Value) = "" Then
                   ' Ignore Null values and zero-length strings.
               ElseIf IsEmpty(Result) Then
                   ' First value found.
                   Result = Value
               Else
                   ' Join subsequent values.
                   Result = Result & Delimiter & Value
               End If
               Records.MoveNext
           Wend
       Else
           ' No records found with the current criteria.
           Result = Null
       End If
       Records.Close
   
   End Sub
   

   전체 문서는 내 문서에서 찾을 수 있습니다 :

   테이블이나 쿼리에서 하나 개의 필드에서 (CONCAT) 값 가입

   읽기 전체 기사 : 해당 링크를 찾아 계정을 가지고 있지 않은 경우.

   코드는 GitHub의에 있습니다 : VBA.DJoin

from https://stackoverflow.com/questions/56764071/concatenating-multiple-rows-with-multiple-values-in-it-into-single-line-in-ms by cc-by-sa and MIT license