MS Access query: Merge multiple columns from multiple rows into single fields

Question

I have one table in MS access 2016.

If the ID is same for two rows, I want to merge the Code & Course columns into single columns.

So the output should be

I have tried functions like STUFF, FOR XML PATH etc which seems to be not compatible with MS Access.

Any help is greatly appreciated.

kiks73 · Answer 1 · 2019-03-01T14:28:28.897

0

In MySQL there is the GROUP_CONCAT aggregate function that does exactly what you are looking for, but not in Access SQL.

The only way to do that is using VBA code. You can take a look here:

Allen Browne wrote a function in VBA to get that result.

In Access you can call VBA functions in your SQL code, so this could be the solution:

SELECT ID,  
       ConcatRelated("Code", "tableName", "ID = " & [ID]),
       ConcatRelated("Course", "tableName", "ID = " & [ID]) 
FROM tableName;

edited Mar 01 '19 at 14:28

answered Feb 28 '19 at 14:31

kiks73

The solution looks like it works for merging a single column but not multiple. – Raj Kumar Mar 01 '19 at 04:44
@RajKumar i've edited the answer adding the Access SQL: you have to call the function 2 times in your SQL code – kiks73 Mar 01 '19 at 14:29

1 Answers1