Ms Access Query: Concatenating Rows through a query

Question

Suppose I have table in Ms Access with following information:

ColumnA ColumnB
1       abc
1       pqr
1       xyz
2       efg
2       hij
3       asd

My question is, how can I concatenate the values in the second column to a row value based on the first column. The query results that I want is as follows:

ColumnA ColumnB
1       abc, pqr, xyz
2       efg, hij
3       asd

I want to achieve this through a query. Can someone help me attain this?

Protoplast

1) Standard SQL has no Concat set function: what would the resulting data type be? would it violate 1NF? would it be a set? how could such data be queried? etc; 2) Access2007 introduced multi-valued types (theregister.co.uk/2006/07/18/multivalued_datatypes_access/…); 3) Have you considered a report? The one that ships with Access is quite nice. – Delapaz

Answer 1

You need a function to do the concatenation.

Microsoft Access condense multiple lines in a table

Example using your data:

Select T.ColumnA
  , GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
From Table1 AS T
Group By T.ColumnA;

Retiarius

I need to achive this in a query and not a vba function. I have stated that in my question. – Protoplast

Read that link. You would create a function and use the function in your query. There is no way to do it otherwise. – Retiarius

But isn't there anyway to do it only in query instead? – Protoplast

@Protoplast - Nope. No way to do it with a dynamic number of child items. – Retiarius

I am trying your solution. But its not giving me any results. – Protoplast

@Protoplast - Typo in the query (didn't use a close quote). – Retiarius

@Protoplast - Posted another revision after setting up a test db to confirm. Should work now. – Retiarius

Answer 2

Here's an outstanding link re: how to do this from within SQL by calling a function. The instructions are exceptionally clear & the function is written for you so you can just copy, paste & go. Even someone with no knowledge of VB can easily implement it: Concatenate values from related records

Gayle

Answer 3

this can be very difficult to obtain. If you MUST do it in a query and not a function, the problem that you will run into is the limit of the number of rows you can concatenate into one column. So far the only way that i have found to achieve this is via iif statements.

SELECT 
test1.ColumnA AS ColumnA, 
First([test1].[ColumnB]) & IIf(Count([test1].[ColumnB])>1,"," & Last([test1].[ColumnB])) AS ColumnB
FROM test1
GROUP BY test1.ColumnA;

returns:

ColumnA  ColumnB 
1      abc,xyz 
2      efg,hij 
3      asd

This will return the first and the last only, but I'm sure with a little work you could work out the Choose function, but like I said you would have to add more iif statements for each additional item you want to add, hence the limitation.

Transmute

Yeah. I would really appreciate if you could post that solution for me. – Protoplast

@Protoplast - The problem with the above solution is that unknown number of child item.s Right now, each item only has 2 column B values. What happens when there are 10? 100? – Retiarius

ultimately.. its determined by how many items you plan on having... if columnA value can have 20 items grouped in columnB... this is probably not your best solution... if your looking at 2 - 3 its possible. – Transmute

@Thomas.. hence the limitation. in practice I have a 3rd item that iif the count is more than two it displays "more..." but it all is determined by the OP's needs and requirements. If he can't use a function for some reason or another... he cant use a function.. and also as mentioned... if he has more than 3 or 4 its not a good solution. – Transmute

@Transmute - Agreed. Not demeaning your solution but rather the approach requested by the OP. – Retiarius

No offense taken... I agree entirely... Access isnt tsql or plsql... I wish it was though.. it would make my life so much easier. – Transmute

In all honesty... I like your solution better, and I will probably implement it in the near future. +1 – Transmute

Could you please explain how to do the same with 3 items ? Choose function not working here @Transmute – Disagreeable

Answer 4

The table could have a sequence column, which gives it a unique primary key of ColumnA-sequence:

table: t1
ColumnA sequence ColumnB
1       1        abc
1       2        pqr
1       3        xyz
2       1        efg
2       2        hij
3       1        asd

And a Crosstab could be created:

query: x1
TRANSFORM Min([columnB] & ", ") AS Expr1
SELECT t1.columnA
FROM t1
GROUP BY t1.columnA
PIVOT t1.sequence;

columnA 1    2    3
1       abc, pqr, xyz,
2       efg, hij,
3       asd,

Then a final query can combine the columns and remove the last comma:

SELECT x1.columnA, Left([1] & [2] & [3],Len([1] & [2] & [3])-2) AS columnB FROM x1;

columnA columnB
1       abc, pqr, xyz
2       efg, hij
3       asd

To automate filling in the sequence, the following VBA code can be used:

Sub fill_sequence_t1()
  Dim i: i = 1
  Do While DCount("*", "t1", "sequence IS NULL") > 0
    DoCmd.RunSQL "SELECT t1.columnA, Min(t1.columnB) AS columnB_min INTO t2" & _
                 " FROM t1 WHERE t1.sequence IS NULL GROUP BY t1.columnA;"
    DoCmd.RunSQL "UPDATE t1 INNER JOIN t2 ON (t1.columnA = t2.columnA)" & _
                 " AND (t1.columnB = t2.columnB_min) SET t1.sequence=" & i
    CurrentDb.TableDefs.Delete "t2"
    i = i + 1
  Loop
End Sub

Hachmin