Query to place names from columns to rows (not crosstab query)

[hunsnowboarder]

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!

[June7]

I think this is going to require VBA code. Options:

1. write records to a 'temp' table and use that table as source for output to Excel, purge the table after each execution - I use this technique for some extraordinary data manipulation, info at http://forums.aspfree.com/microsoft-...ry-322123.html

2. concatenate the owners to a single string, review http://allenbrowne.com/func-concat.html

[hunsnowboarder]

I think this is going to require VBA code. Options:

1. write records to a 'temp' table and use that table as source for output to Excel, purge the table after each execution - I use this technique for some extraordinary data manipulation, info at http://forums.aspfree.com/microsoft-...ry-322123.html

2. concatenate the owners to a single string, review http://allenbrowne.com/func-concat.html

Wow! Thank you soo much! It seems you really understand my problem (many people didn't). The second solution would be perfect for me as in excel I will be able to make columns from rows.

I will try it, however at this moment seems very complicated to me.

Thank you again, I will get back with my progress.

Thanks again!