MS Access SQL Query to Concatenate or Consolidate Related Fields

Question

If I had the following MS Access table and sample data,

Status tbl
UpdateID PK
CustomerNo text
StatusType text
UpdateDate date

UpdateID, CustomerNo, StatusType, UpdateDate
001, 0099, Open, 2011-01-01
002, 0099, Pend, 2011-01-02
003, 0100, Open, 2011-01-03
004, 0099, Appr, 2011-01-04
005, 0100, Pend, 2011-01-05
006, 0099, Clsd, 2011-01-07

then how could I write a query that would result in the following consolidated/concatenated output?

CustomerNo, UpdateDate
0099, 2011-01-01;2011-01-02;2011-01-04;2011-01-07
0100, 2011-01-03;2011-01-05

I realize now that UpdateDate being of type date may complicate the solution, but if treating it as text would simplify the answer then that would suffice. — duckah, Jan 24 '11 at 18:39

score 2 · Accepted Answer · answered Jan 24 '11 at 18:48

2

There is no convenient way to do this in Access without using code to iterate over the returned rows and build the string yourself.

Here is some code that will help you do this:

Public Function ListOf(sSQL As String, Optional sSeparator As String = ", ") As String

  Dim sResults As String
  Dim rs As DAO.Recordset

  Set rs = CurrentDb.OpenRecordset(sSQL)

  While Not rs.EOF

    If sResults = "" Then
      sResults = Nz(rs.Fields(0).Value, "???")
    Else
      sResults = sResults + sSeparator & Nz(rs.Fields(0).Value, "???")
    End If

    rs.MoveNext

  Wend

  ListOf = sResults

End Function

And here is how you can use it in an Access query:

 SELECT [CustomerNo], 
 (ListOf('SELECT [UpdateDate] FROM StatusTbl WHERE CustomerNo = ' + CStr([CustomerNo]))) AS UpdateDates
 FROM StatusTbl

Note that this only works if you're executing the query in Access, queries executed from (for instance) an ADO connection will not have access to the ListOf function.

answered Jan 24 '11 at 18:48

Larry Lustig

49,320
14
110
160

I added this function to a Module and saved it, then updated my query as shown. Attempting to run the query returns the compile error: "User-defined type not defined", and points to the line "Dim rs As DAO.Recordset". Is the issue here with "DAO"? – duckah Jan 24 '11 at 19:02
@duckah - reference DAO under: Tools | References in the Visual Basic editor – JeffO Jan 24 '11 at 19:09
@duckah — @Jeff O is correct. You must let your Access project know you want it to use the DAO library. – Larry Lustig Jan 24 '11 at 19:14
@Jeff O - thank you, I added the "Microsoft DAO 3.6 Object Library", however now I get Run-time error '3061': "Too few parameters. Expected 1.", pointing to "Set rs = CurrentDb.OpenRecordset(sSQL)". – duckah Jan 24 '11 at 19:19
It looks like removing the single quotes from my ListOf parameter allows it to work, but then I get "At most one record can be returned by this subquery," which I believe is an error related to grouping and aggregating. Any advice? – duckah Jan 24 '11 at 19:22
@duckah, please include view you're using to call ListOf. It sounds like your argument sSQL may be empty. – Larry Lustig Jan 24 '11 at 19:23
I think I fixed that issue now, and although I understand the purpose of the if-then-else statement I am not sure of how to replace "???" with the table data. I am currently guessing & checking how to pull the appropriate data. – duckah Jan 24 '11 at 19:40
You do not need to replace the "???". It's there as a placeholder in case any of the records return a NULL value (you can also put "NULL" if you think that will be clearer). Just make sure that your SQL statement returns the column you want aggregated as the first (and preferably only) column on each row. Then the `rs.Fields(0).Value` will take care of getting the value from that column for you. – Larry Lustig Jan 24 '11 at 19:50
You could get rid of the If/Then/Else test for concatenating and replace it with `sResults = sResults + sSeparator & Nz(rs.Fields(0).Value, "???")` and then `sResults = Mid(sResults, Len(sSeparator)+1)`. That is, you'd just concatenate everything starting with the separator string and then strip off the first one at the end. – David-W-Fenton Jan 25 '11 at 04:42

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

This comes up fairly often, here is a previous take: Combine rows / concatenate rows

edited May 23 '17 at 11:45

Community

1
1

answered Jan 24 '11 at 20:48

Fionnuala

90,370
7
114
152

MS Access SQL Query to Concatenate or Consolidate Related Fields

2 Answers2