many results in a concatenated field

[potts]

I need help in creating a reference list.

I have a main 'Articles' table which forms a many-many relationship with an 'Authors' table, through an 'ArticleAuthors' table.

Generally speaking, I'm not too bad with the workings of reports, but I need to create a concatenated field that will hold the title from the 'Articles' table and all the associated authors from the 'Authors' table. The concatenation I can handle, but not the multiple authors as the number held changes from article to article (though there will always be at least one author).

does anyone know how to handle the multiple authors?

Thanks

 

[pdx_man]

How's your VBA? You are going to have to build the string. Something like this:

Set dbs = CurrentDb
MyStr = ""
SqlStr = "Select Authors From 3_Table_Aggregate_Query Where Titles = '" & Me.Titles & "'"
Set rst = dbs.OpenRecordset(SqlStr)
Do While Not rst.EOF
MyStr = MyStr & rst!Authors & ", "
rst.MoveNext
Loop
rst.close

Me.MyControlName = Me.Titles & " --- " & Left(MyStr,Len(Mystr)-2)