Many to Many Relationships Displayed in one Row

[Gouda42]

I have looked for this online before but wasnt able to find anything. I have a many to many relationship and i want to display it in on single row, right now my many to many table looks like this

LegalID1 - Strategy1
LegalID1 - Strategy2
Legal ID2 - Strategy3

etc..
I want it to look like

LegalID1 - Strategy1 - Strategy2

obviously being in separate columns

I looked into a pivot but wasnt able to find sufficient enough information to build it out. I understand that it might be multiple queries. Any help would be appreciated

 

[pbaldy]

Does a concatenated list work, rather than separate columns?

http://allenbrowne.com/func-concat.html

 

[Mark_]

Philosophical problem with your request;
In a many to many, you are not restricted in how many you have. Are you intending to support 154 columns for this? If yes, how about when it hits 782?

 

[Gouda42]

Yes any way would work doesnt need to be in separate columns. at most it would be 5 columns but hundreds of rows, they wouldnt have more that 5 strategy's.

That VBA is a little hard for me to follow.

 

[pbaldy]

That VBA is a little hard for me to follow.

You just copy it to a standard module and call it as shown.

 

[theDBguy]

Yes any way would work doesnt need to be in separate columns. at most it would be 5 columns but hundreds of rows, they wouldnt have more that 5 strategy's.

That VBA is a little hard for me to follow.

Hi. Would this simple function be any easier for you instead?

 

[gemma-the-husky]

Why do you think you want it this way in particular?

As opposed to a 2 column solution with multiple values in one

ie Either
LegalID_Number, list of strategy IDs
OR
StrategyID_Number, list of Legal IDs