Query to put multiple records in multiple columns

[hunoob]

Hi there Everyone.

I hope you can help me with this problem.

I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).

The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. Please see the attached file for better understanding!

What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.

The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).

Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.

Please see the attached file for better understanding!

Thank you in advanc for your help!

 

[michaeljryan78]

You are looking to a crosstab query. Create a new query, go to SQL view and Paste this:

TRANSFORM Count(the_Query.[Risk_ID]) AS CountOfRisk_ID
SELECT the_Query.[First Name], Count(the_Query.[Risk_ID]) AS [Total Of Risk_ID]
FROM the_Query
GROUP BY the_Query.[First Name]
PIVOT the_Query.[Risk topic];

 

[hunoob]

Hi michaeljryan78!

Thank you very much for your help! Actually I wanted to have it grouped by the risks, so I tried to make the adjustments...but failed in succeding.

What I would like to have is this:

RiskNo-------RiskDescription--------RiskOwner1-------Department1-----RiskOwner2-----Department2----etc.
1.------------Key person risk-------Ben----------------Accounting-------Jane------------HR------------------
2.------------Sales risk-------------Fred---------------Sales---------------------------------------------------
3.------------Tax risk--------------Ben----------------Accounting---------------------------------------------

Is it possible to have it in this format? Could you show me how?


Edit: Honestly I do not really think that the crosstab query is a good solution. In the real table there are more than 120 people. So I should add all the people in order to see if they are relevant to a risk or not....
But what I want to have is to have just five extra columns (in this trial db just 3 extra columns) with the header Owner1, Owner2, Owner3, etc. and the name of the owners displayed in that column. In the rows I have the risks in the columns the owners and their departments. But I want to have only three/five columns which display the names. This means that Owner1 for Risk1 is Ben, but for Risk2 Owner1 could be Jane or John (same column shows a different name).
I hope this way is more understandable what I am looking for.
Thank you!

Thank you again for your help!