469,603 Members | 2,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

grouping multiple records on joined table

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

Sep 5 '07 #1
1 1326
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 <pipe.j...@gmail.comwrote:
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

Sep 5 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
6 posts views Thread by Christopher Harrison | last post: by
reply views Thread by Stephen | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.