I've an Access database that follows each Line Item across multiple stages where tasks are assigned to individuals. Each stage has a Comments field and are recorded in a Table which looks like:
Line Item Stage Title Comments
1 1 Introduction Trial comment
1 1 Introduction Another one
1 2 Abstract Following one
1 2 Abstract Andi nexto
1 3 Thesis Nexto
2 1 Introduction Comment for next item
2 1 Introduction Andi another one
...
I want to be able to concatenate these comments for each stage and each Line Item as:
Line Item Stage Title Comments
1 1 Introduction Trial comment, Another one
1 2 Abstract Nexto one, Andi next
1 3 Thesis Nexto
2 1 Introduction Comment for nexto item, Andi another one
I tried using Allen Brown's ConcatRelated() function with multiple WHERE criteria:
ConcatRelated("[Comments]","[CommentsT]","[LineItemNo]=" & "[txtLineItemNo] AND "[StageNo]=" & [txtStageNo])
but with no luck. Using a single WHERE clause does concatenate all the comments in the required field without considering StageNo and Title.
Kindly advise as to what is the best way for me to achieve this.
Thank you.