Consolidating records on many side of a one-to-many relationship into one row for mail merge

[markaschiff]

Hi all,

I'm trying to build a database to track interviews. I'm calling the interviewees participants. One interview may involve between 1 to 4 participants. Each participant will (over time) be involved in many interviews. I'm thinking of the following table structure:

tblInterviews
InterviewID
InterviewDate
InterviewStartTime
InterviewStopTime
InterviewOutcome

tblParticipants
ParticipantID


ParticipantPrefix
ParticipantFirstName
ParticipantLastName
ParticipantDOB

tblInterviewsParticipants
InterviewParticipantID
InterviewID
ParticipantID
ParticipatingVia (i.e., Zoom, in person, phone)

After each interview, I need to write a letter that has the following sentence: "I interviewed ParticipantID1, ParticipantID2... (up to 4 participants) on InterviewDate. The interview commenced at InterviewStartTime and concluded at InterviewStopTime..." I'm running into trouble doing this using Mail Merge and Word.

Is there a query or some other way that I could output a table such that for each InterviewID in tblInterviewsParticipants, each related ParticipantID is put onto one row? For example, right now, the join table would look like:

InterviewID1 ParticipantID1
InterviewID1 ParticipantID2
InterviewID2 ParticipantID1

I need a table that looks like:

InterviewID1 ParticipantID1 ParticipantID2
InterviewID2 ParticipantID1, etc.

Any assistance will be appreciated. FYI, I'm a newbie and unfortunately not versed in VBA.

[June7]

Need VBA to build the CSV string. Review http://allenbrowne.com/func-concat.html.
Call the function from query or textbox or VBA.

Is Word merge really needed? Could an Access report provide adequate output?

[Welshgasman]

Here is another function by theDBguy http://www.accessmvp.com/thedbguy/co...itle=simplecsv

[CJ_London]

usual method is to use a function something like the concat_related function here
http://allenbrowne.com/func-concat.html

your vba code might look something like

dim myLetter as string
myLetter=" "I interviewed " & concat_related(.....) & " on interview date"

[moke123]

I use Albert Kallal's Super Easy Word Merge routine to generate various documents.

What I do is use a non-normalized table in a temporary database. I use a series of append queries to gather the data into that table and then just use "Select * from TempDB" to populate the merge fields.

here's an old demo