Query - Concatenate multiple results

[markman123]

Hi All,

I'm trying to create a query in Access - not sure if a Macro is required, or if this is just a straight up SQL query.

The dilemna I have is I'm OUTER JOINING two tables, one named 'Projects' and the other 'Comments'. There may be several Comments for each Project, and they have enforced referential integrity between 'Projects.ProjectID' and 'Comments.ProjectID'.

What I need done is return a list of all the projects, and a concatenation of the comments in the comments field, so as to return all of the comments in one Row. I'm lost as to how to google this...

Here is what I have so far (if it helps)



SELECT Project.ProjectID, Project.ProjectName, Comments.Comment
FROM Project LEFT JOIN Comments ON Project.ProjectID = Comments.ProjectID

Can this be done?

While I'm here, is it possible to have an "LEFT JOIN WHERE" kind of clause? I've had no success because I don't understand the parenthesis required in Access.

Thank you,

Mark

[OBP]

I Don't think that you can do this with a Query.
You can do it using a Recordset on a Form.
The other alterantive is to use an Update Query to put the data in a Temporary table to display the concatenated data and then delete the records once they have been viewed or when you run it again.

[markman123]

Thanks OBD - appreciate the time to reply.

I assume a recordset would be a subform datasheet within a form which shows a list of the comments?

Regards,

Mark

[OBP]

Mark, yes it would populate an Unbound field on the subform.
The VBA would go in to the Subform's On Current event and would be a Recordset based on the Current Mainform Record and loop through the Comment Records.
Let me know if you need some example code.