Denormalizing data

I have the gollowing table

TEAM      |   MEMBER

Team1    |   Member1
Team1    |   Member2
Team1    |   Member3

I would like to have one row per team as follows:

Team1  |    Member1   |    Member2   |    Member3   |

How do I do that in MS Access Query Design?
fitalianoAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need to pass the WHERE argument:

MemberList:DConcat("[Member]","TEST","[Team] = '" & [Team] & "'")

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and just to add a bit, subqueries are the only way to do it without writing a function and that is not a very elegant way of doing this.

You also could write records out to a temp table and fill columns sequentially, but that's not a good way to do it either really.

The DConact() function in Patrick's article is the simplest method that I've seen.

Jim.
0
 
fitalianoAuthor Commented:
Thank you Jim, what syntax should I use? DConcat([MEMBER]) doesn't seem to work
0
 
fitalianoAuthor Commented:
Jim I  tried

MEMBER: DConcat("MEMBER","TEST")

and I get ALL the other members in one column, which is not what I want. I only want the members belonging to a Team

+ I need them in different columns not one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.