Converting a list of fields to a single field

[rudolf.hever]

Hi,

I would like my database to keep track of each meeting and who out of
our department attends the meeting (number of attendees changes).

I have three tables. One records the details of the meeting. One
containees all attendees (ie people in the department) and one is a
join table that records the attendee for each meeting.

tblMeeting records the MeetingID (primary key), date, meeting purpose
etc.

tbleMeeting Attendee records the Meeting ID and Attendee ID.

and tbleMeeting Attendee lists all the people in the department

The best result I can get through a query is
Meeting 1, John
Meeting 1, Alison
Meeting 1, Jack
Meeting 1, Natalie

Ideally, the end result i would like is to have is a report or table,
where the first column is MeetingID, and the second column lists all
the attendees eg "John, Alison, Jack, Natalie". E.g
Meeting 1, John Alison Jack Natalie

I think i have my tables set up correctly, but dont know how to go
about turning a one to many field situation into just two fileds ie
where the "many" field is a text field, and i would like to convert
those many text fileds into one field

If you could point me in the right direction that would be great.

Thanks in advance.

Rudolf

 

[Douglas J. Steele]

It can't be done using SQL alone: you have no choice but to create a
function in VBA and call that function in your query.

See, for example, http://www.mvps.org/access/modules/mdl0004.htm at "The
Access Web"

 

[rudolf.hever]

Hi Ken,

A multi-column report was exactly what I was after. Worked like a
charm.

Thanks heaps!

Rudolf