Hi there Everyone!
I am using Access not too long ago and I would like to make a query for a report.
The situation is the following:
I have a table with different risks and I need to add the name of employees as risk owners to their relevant risks. It is a many to many relation (one risk can belong to many persons, and one person can be owner of multiple risks as well), therefore I made a junction table which adds the risk no. to the relevant names.
In the attached database file you can see clearly these tables. My problem is that when I make a query on risks (I already created this query in the file) I get the results in rows....meaning that one risk has as many rows as risk owners. However I would like to have only one row per risk and then a different column for each risk owner. The maximum risk owners per risk is 5, so I would like to have something like what you can see in the "Query_should_look_like_this" table. The crosstab query is not a solution because there must be five columns (RiskOwner1, RiskOwner2, RiskOwner3, RiskOwner4, RiskOwner5) but there are more than 200 employee names in the real database, so each column holds different names per risks. For those risks that have less than 5 owners some of columns remain blank.
What I would like to get as end result is like the "Query_should_look_like_this" table in the file.
Is it possible to make it? Please try to help if you can because I struggling for days now and I am very desperate.
P.S.: I need this format exactly because my boss accepts my reports only in this format (in excel).
Thank you very much in advance!