How to make a summary of every duplicate value in a new column?

Question

I need a column in my table (Microsoft Access) that has a summary of the ID of every duplicate value in the table. The table has a parent and child type, the parent type needs the summary of every child ID in the table.

I tried nesting the expression in a query and adding the ID for every duplicate it found ([summ] & ", " & [id]), but this gives me a circular reference error.

My table looks like this:

ID | name | type  | 
---+------+-------+
 1 | aaa  | parent| 
 2 | aaa  | child | 
 3 | aaa  | child | 
 4 | bbb  | parent| 
 5 | bbb  | child |
 6 | bbb  | child |

I need a new column that would give me this:

ID | name | type   | summ   
---+------+--------+------
 1 | aaa  | parent | 2,3    
 2 | aaa  | child  | 
 3 | aaa  | child  | 
 4 | bbb  | parent | 5,6
 5 | bbb  | child  |
 6 | bbb  | child  | 

I know it'd be better practice to have another table for this, but I need to be able to export it in this format.

Thanks in advance.

Answer 1

This needs two steps:

1 - Build a query using ConcatRelated that returns all records with type=child, grouped by name

Result:

aaa | 2,3
bbb | 5,6

2 - Use LEFT JOIN to connect the result with the original table (parent records). E.g.

SELECT ...
FROM table LEFT JOIN concatquery
ON (table.name = concatquery.name) AND (table.type = "parent")

Actually I'm not sure if this will work. If it doesn't, use INNER JOIN, and then UNION it with the type=child records.