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

 

[Larry Daugherty]

MS tells you that you can have multiple values in a single field.
That's contrary to the Relational rules. In fact, MS obeys the
relational rule properly in this case but goes out of their way to
misrepresent and obscure what they are actually doing. In Relational
Database Management Systems the rule is that fields are "atomic": one
field equals one instance of a single fact.

Microsoft has deliberately made some humongous blunders in corrupting
parts of the relational model in order to claim that Access is
"powerful yet easy to use". In fact, those "features" lead novices
astray and cause them endless grief in coming to grips with the fact
that they've been fed lies and then that they have to go back and
clean up the mess. Lookup Fields are another recent egregious
example. Use those "features" at your peril.

There can be some value in showing all of the values in a given field
in a recordset in a single textbox in a Report and I've done so. That
doesn't violate any Relational rule.

I recommend that you create a higher order person table with a related
color table. Run your queries on those tables and play with the
results as you wish.

HTH

 

[Pieter Wijnen]

That said a Simple Function Will Do

Public Function GetColorsForID(ByVal ID As Long) As Variant
Dim Res As Variant
Dim Db AS DAO.Database
Dim Rs As DAO.Recordset

Res = Null
Set Db = Access.CurrentDB
Set Rs = Db.OpenRecordset("SELECT Color FROM TableB WHERE ID=" & ID,
DAO.dbOpenSnapshot)
While Not Rs.EOF
Res = (Res + ", ") & Rs.Fields(0).Value
Rs.MoveNext
Wend
Rs.Close: Set Rs = Nothing
Set Db = Nothing
GetColorsForID = Res
End Function

HTH

Pieter

PS I Will not reccomend you to store the result in Table A though...