Query with line break divided lines of data?

[securitywyrm]

I have a contacts database that has position and program titles for many individuals. Some people have dozens of positions, some just have one. I want to create a query that would output something like this

Contact email | Program and position title (Line break) Program and position title (line break) Program and position title)
Live example
John Smith | Nephrology Program coordinator (Line break) Rheumatology Site Director (Line break) Rheumatology TMS Coordinator
Jane Doe | Rheumatology program coordinator (Line break) Cardiology Site director (Line break) Urology program coordinator



That way I can just feed that into a mail merge document and email each person a list of their current positions. Is this possible with SQL queries?

[Gicu]

Yes, it is, look at the Join function (https://msdn.microsoft.com/en-us/vba.../join-function). I also have a sample custom function on my site (http://forestbyte.com/vba-code-samples/) called JoinFromArray that I use in queries. I assume the positions are in a different table with a ContactID as a foreign key.



Cheers,
Vlad

[securitywyrm]

That's some amazing code. It's a few orders of magntiude above what I can do, but I THINK I understand WHAT it does, if you could confirm.

Let's say we have a table of "Names and email addresses" and a table of "Names, position title and program name."

This function will let you take the output of the second query (Name, position title, program name), and merge it into a column output of the first query. Thus it would be "Name | Email address | Array from second query matching name value"

Is that correct?

[Gicu]

Yes, you pass it the foreign key field name, the value (from the first query), the name of the second query, the delimiter, etc.

[securitywyrm]

Yes, you pass it the foreign key field name, the value (from the first query), the name of the second query, the delimiter, etc.

Fantastic. I sent in an email requesting a quote from your site, seems like you have all the code I need implemented into these databases.

[Gicu]

Is QBFMailingList the query where you want the new field?