Grouping query assistance required. (1 Viewer)

[troy79]

Hi I am looking for assistance in a query where I can group by COL1 and in COL2 it will find all data relating to the COL1 cell and group with a comma separating each cell.

Example

COL1 COL2
1 AA
2 BB
3 CC
4 DD
5 EE
1 FF
2 GG
3 HH
4 II
5 JJ

Outcome required

COL1 COL2
1 AA, FF
2 BB, GG
3 CC, HH
4 DD, II
5 EE, JJ

Any help would be greatly appreciated.

 

[June7]

Common topic. Requires VBA. Review http://allenbrowne.com/func-concat.html

If you prefer a CROSSTAB, consider:

TRANSFORM Min(Table4.Col1) AS MinOfCol1
SELECT Table4.GrpID
FROM Table4
GROUP BY Table4.GrpID
PIVOT DCount("*","Table4","GrpID=" & [GrpID] & " AND ID<=" & [ID]);

This requires a unique record identifier field, autonumber field will serve.