concatenate values in a continuous form

[Michelle]

Hi all

I have a form with two subforms

The main form has a combo box which lists ShiftPatternID.

When user selects from combo first subform shows shift pattern for that
ID, ie user selects, say, TL the first subform shows shifts that would
be worked monday to sunday for that ID, eg Monday: 09.30 - 17.00,
Tuesday: 09.30 - 17.00, Wednesday: Off etc.

I want the second subform to show all agents working that shift pattern
and which week numbers they will work it. At the moment my second
subform shows

AgentName WeekNum
eg
Michelle Beatty 1
Michelle Beatty 2
Michelle Beatty 5
Fred Bloggs 1
Fred Bloggs 7

in a long list. I want it to display data as follows:

Michelle Beatty 1, 2, 5
Fred Bloggs 1, 7

Does anyone know how I can concatenate the WeekNum values into one field
on the second subform.

Alternatively can anyone think of a better way of displaying this data.
:-)

Thanks
Michelle
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!