将行串联为1-Access 2007 [英] Concatenate rows into 1 - access 2007

问题描述

我正在Access 2007中建立一个数据库，目前我正在为查询而苦苦挣扎.在仅一列中，有可能提出多个答案.因此，当我打开查询时，当给出3个答案时，我得到3行.我想将行连接成一列.有时没有答案，有时1或2或最多3.

I'm making a database in Access 2007, and currently I'm struggling with a query. In only one column, it is possible to cose multiple answers. So, when I open the query I get 3 rows when 3 answers were given. I would like to concatenate the rows into one. Sometimes there is no answer, sometimes 1 or 2 or maximum 3.

目前我的查询如下:

列名:B-Latijnse命名(空格)B-Nederlandse命名(空格)Bloemen.B-4月2日.值(空格)B-Klas(空格)-B-Leerkracht(空格) B-Locatie

COLUMNNAMES: B - Latijnse benaming (space) B - Nederlandse benaming (space) Bloemen.B - April 2.Value (space) B - Klas (space) - B - Leerkracht (space) B - Locatie

ROW 1 Abelmochus(空间)(空间)Zaaien(空间)1ATA(空间)Depuydt(空间)Serre 1

ROW 1 Abelmochus (space) (space) Zaaien (space) 1ATA (space) Depuydt (space) Serre 1

ROW 2 阿贝尔莫克斯(空间)(空间)Oogsten(空间)1ATA(空间)Depuydt(空间)Serre 1

ROW 2 Abelmochus (space) (space) Oogsten (space) 1ATA (space) Depuydt (space) Serre 1

ROW 3 阿贝尔莫丘斯(空间)(空间)贝美斯滕(空间)1ATA(空间)Depuydt(空间)Serre 1

ROW 3 Abelmochus (space) (space) Bemesten (space) 1ATA (space) Depuydt (space) Serre 1

我希望它看起来像这样:

I would like it when it looks like this:

ROW 1 Abelmochus(空间)(空间)Zaaien，Oogsten，Bemesten(空间)1ATA(空间)Depuydt(空间)Serre 1

ROW 1 Abelmochus (space) (space) Zaaien, Oogsten, Bemesten (space) 1ATA (space) Depuydt (space) Serre 1

tbl称为Bloemen，这是当前查询的SQl:

The tbl is called Bloemen and this is the SQl of the query for the moment:

  SELECT Bloemen.[B - Latijnse benaming], Bloemen.[B - Nederlandse benaming], Bloemen.[B - April 2].Value, Bloemen.[B - Klas], Bloemen.[B - Leerkracht], Bloemen.[B - Locatie]
  FROM Bloemen
  WHERE (((Bloemen.[B - April 2].Value) Is Not Null));

任何人都可以帮助我将行连接成一列.我不知道如何编写SQL ...我必须对48个查询执行此操作，因此当它是一种标准时，将对您有所帮助.

Can anyone help me to concatenate the rows into one. I don't know how to write a SQL... I have to do this for 48 querys, so I would be helpfull when it's kind of standard.

非常感谢！！！！！

Jonas Roelens

Jonas Roelens

推荐答案

如何:

下面的代码示例将从SQL或字符串输入返回定界列表.这是在查询中使用它的方法:

The code sample below will return a delimited list from either SQL or string input. This is how to use it in a query:

   SELECT documents.MembersOnly, 
       ConcatList("SELECT FName From Persons WHERE Member=True",":") AS Who, 
       ConcatList("",":","Mary","Joe","Pat?") AS Others
   FROM documents;

或

   SELECT tc.Company, 
          ConcatList("SELECT Contract 
                      FROM tblservices 
                      WHERE CompanyKey=" & tc.[CompanyKey],", ") AS Contracts
   FROM tblCompanies tc

示例代码

   Function ConcatList(strSQL As String, strDelim, _
            ParamArray NameList() As Variant)
   ''Reference: Microsoft DAO x.x Object Library
   Dim db As Database
   Dim rs As DAO.Recordset
   Dim strList As String

   Set db = CurrentDb

   If strSQL <> "" Then
       Set rs = db.OpenRecordset(strSQL)

       Do While Not rs.EOF
           strList = strList & strDelim & rs.Fields(0)
           rs.MoveNext
       Loop

       strList = Mid(strList, Len(strDelim) + 1)
   Else

       strList = Join(NameList, strDelim)
   End If

   ConcatList = strList

   End Function

来自: http://wiki.lessthandot.com/index.php /Concatenate_a_List_into_a_Single_Field_％28Column％29

上面的链接现在包括下面的ADO版本，当从未添加版本时，我将其作为@oneday包含在这里.

The link above now includes the ADO version below, which I include here as @onedaywhen never added a version.

这更快，更容易，但是对字段(列)名称更加敏感.您需要确保它们不是保留字. ADO版本的另一个优点是您不必限制要连接的列数:

This is faster and easier, but a lot more sensitive about field (column) names. You need to ensure that they are not reserved words. Another advantage of the ADO version is that you are not limited in the number of columns to be concatenated:

SELECT d.DeptID, d.Department, 
       ConcatADO("SELECT FName & ' ' & SName, Address FROM Persons 
                 WHERE DeptID=" & [d].[DeptID],", "," : ") AS Who
FROM Departments AS d INNER JOIN Persons AS p ON d.DeptID = p.DeptID
GROUP BY d.DeptID, d.Department, 3;

Function ConcatADO(strSQL As String, strColDelim, _
         strRowDelim, ParamArray NameList() As Variant)
Dim rs As New ADODB.Recordset
Dim strList As String

On Error GoTo Proc_Err

   If strSQL <> "" Then
       rs.Open strSQL, CurrentProject.Connection
       strList = rs.GetString(, , strColDelim, strRowDelim)
       strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
   Else
       strList = Join(NameList, strColDelim)
   End If

   ConcatADO = strList

Exit Function

Proc_Err:
    ConcatADO = "***" & UCase(Err.Description)
End Function

这篇关于将行串联为1-Access 2007的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！