Concatenate rows into 1 - access 2007

Question

Hello,

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.

For the moment my query looks like this:

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 (space) (space) Zaaien (space) 1ATA (space) Depuydt (space) Serre 1

ROW 2 Abelmochus (space) (space) Oogsten (space) 1ATA (space) Depuydt (space) 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 (space) (space) Zaaien, Oogsten, Bemesten (space) 1ATA (space) Depuydt (space) Serre 1

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));

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.

Thanks a lot!!!!!!!

Jonas Roelens

Answer 1

"Standard" SQL won't do this, I think. There may be some features in Access to return an array with column values... but unless someone else comes up with a cool suggestion based on such a capability, I'd say "no".

This kind of processing normally has to be done in the programming environment that called the SQL. Are you using some kind of report generator such as BIRT or are you doing "straight" JDBC? If the latter, then you can simply loop through your ResultSet to put that compound column together.

Answer 2

How about:

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;

Or

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

Sample Code

   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

From: http://wiki.lessthandot.com/index.php/Concatenate%5Fa%5FList%5Finto%5Fa%5FSingle%5FField%5F%28Column%29

Answer 3

Thanks to everyone who has answered my question.