Results listed in rows vs in columns

[Steve S]

I have a report that shows the athletes in a sporting event and their
coach’s name if the coach is also a judge/referee at this event. The poupose
is so that when seting up the competition we can ensure the athlete will not
be in a lane where their coach will be judging them.

Event A
Level 2 8 Athletes
Athlete 1
Athlete 2 Coach 1
Athlete 2 Coach 2
Athlete 3
Athlete 4
Athlete 5 Coach 1
Athlete 5 Coach 3
Athlete 5 Coach 7

This distorts the Count of athletes. There are only 5 Athletes but the
report shows 8.

What the user wants is :

Event A
Level 2 5 Athletes
Athlete 1
Athlete 2 Coach 1, Coach 2
Athlete 3
Athlete 4
Athlete 5 Coach 1, Coach 3, Coach 7

How can this be done? Any and all help is appreciated.

thanks

 

[Allen Browne]

This needs some code to concatenate the related records together into a
string.

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[Steve S]

thanks very much. That code is soooo slick and is exactly what I needed.

thanks again.

Steve

 

[Steve S]

I have gotten the Concat function working great on a table but am having a
problem getting it to work on a query. The sql string that is genterated
works fine in the immediate window so I assome I have the parms in the
function call coded correctly but when the function itsself funs i get an
error the error is "To few parameters, expected 1" This message seems to be
coming from the line
set rs.db =(.....

Any help appreciated

 

[Allen Browne]

Posted a follow up to your new thread.