grouping multiple records on joined table

[JackpipE]

I have 2 tables
TABLE A
| id | name |
| 1 | John |
| 2 | David |
| 3 | Adam |

TABLE B
| id | colors |
| 1 | blue |
| 1 | red |
| 1 | yellow |
| 2 | blue |
| 3 | green |
| 3 | black |

When I join the tables by ID I get
| 1 | John | blue |
| 1 | John | red |
| 1 | John | yellow |
| 2 | David | blue |
| 3 | Adam | green |
| 3 | Adam | black |

I would like to merge all the records from table B into one field for
each record from table A:
| 1 | John | blue, red, yellow |
| 2 | John | blue |
| 3 | Adam | green, black |

Thanks for all help,
Jack

[datasponge]

Hi Jack,

This page has two modules that I think will work for you:

http://www.mvps.org/access/modules/index.html

Try:

Concatenate fields in same table

or

Return a concatenated list of sub-record values

Good luck,
Shira

On Sep 4, 9:19 pm, JackpipE <[e-mail]:

I have 2 tables
TABLE A
| id | name |
| 1 | John |
| 2 | David |
| 3 | Adam |

TABLE B
| id | colors |
| 1 | blue |
| 1 | red |
| 1 | yellow |
| 2 | blue |
| 3 | green |
| 3 | black |

When I join the tables by ID I get
| 1 | John | blue |
| 1 | John | red |
| 1 | John | yellow |
| 2 | David | blue |
| 3 | Adam | green |
| 3 | Adam | black |

I would like to merge all the records from table B into one field for
each record from table A:
| 1 | John | blue, red, yellow |
| 2 | John | blue |
| 3 | Adam | green, black |

Thanks for all help,
Jack