Listing multiple child fields in one field

[friday13]

I have a problem where I am trying to make a simple list of child values in one field. For example, rather than having a query of Courses and CourseInstructors give these results:

+-------------+----------------+------------------------+
| Course | Instructor | Percent Responsibility |
+-------------+----------------+------------------------+
| AAAB 1001 | Smith | 100 |
+-------------+----------------+------------------------+
| AAAB 2000 | Smith | 50 |
+-------------+----------------+------------------------+
| AAAB 2000 | Brown | 20 |
+-------------+----------------+------------------------+
| AAAB 2000 | Green | 30 |
+-------------+----------------+------------------------+
| AAAB 2001 | Brown | 100 |
+-------------+----------------+------------------------+
I need to get the results in this format:


+-------------+-----------------------------------------+
| Course | Instructor(s) (Percent Responsibility) |
+-------------+-----------------------------------------+
| AAAB 1001 | Smith (100%) |
+-------------+-----------------------------------------+
| AAAB 2000 | Smith (50%), Brown (20%), Green (30%) |
+-------------+-----------------------------------------+
| AAAB 2001 | Brown (100%) |
+-------------+-----------------------------------------+

I can't really figure it out. How do I get the course instructor data out and into a comma-separated indefinite list in the query? Any help or directions would be greatly appreciated.

Thanks,

Matt

 

[RichMorrison]

Matt,

You can get the general layout you want with a "cross tab" query.

Look into your "Help" from Access or try a search with the key word "crosstab" or "cross" and "tab".

RichM